package com.iris.live.services.data.repositories.impl;

import com.google.common.base.Strings;
import com.google.common.collect.Iterables;
import com.google.common.collect.Maps;
import com.iris.live.services.common.JsonObjectUtils;
import com.iris.live.services.common.StringUtils;
import com.iris.live.services.data.repositories.InsuranceRepository;
import com.iris.live.services.models.PageData;
import com.iris.live.services.models.RequestBase;
import com.iris.live.services.services.InsuranceService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by xin on 2016/7/30.
 */
@Repository
@Transactional//(readOnly = true)
public class InsuranceRepositoryImpl extends BaseRepository implements InsuranceRepository {
    public static final Logger logger = LoggerFactory.getLogger(InsuranceRepositoryImpl.class);

    // 主界面保险
    @Override
    public PageData getInsuranceInfoList(String liveName,String permit,InsuranceService.GetInsuranceInfoListRequest request,Integer pageSize,Integer pageIndex) {
        String sql = " " +
                "  SELECT \n" +
                " a.保险编号,\n" +
                " a.投保类型,\n" +
                " b.state as 保险销售状态,\n" +
                " e.顾客姓名 as 客户姓名,\n" +
                " e.电话号码 as 客户电话,\n" +
                " f.人员姓名 as 销售顾问,\n" +
                " h.级别 as 意向车系,\n" +
                " h.车型 as 意向车型,\n" +
                " h.车型详细 as  意向车款, \n" +
                " a.交强险保险公司 as 交强险保险公司, \n" +
                " a.商业险保险公司 as 商业险保险公司, " +
                " ifnull(xx.交强险保单号,a.交强险保单号) as 交强险保单号,\n" +
                " ifnull(yy.商业险保单号,a.商业险保单号) as 商业险保单号,\n" +
                " IF(xx.交强险开始日期 IS NOT  NULL ,xx.交强险开始日期 , a.`交强险开始日期` ) 交强险开始日期,\n" +
                " IF(xx.交强险结束日期 IS NOT  NULL ,xx.交强险结束日期 , a.`交强险结束日期`   ) 交强险结束日期,\n" +
                " IF(yy.商业险开始日期 IS NOT  NULL ,yy.商业险开始日期 ,  a.`商业险开始日期`  ) 商业险开始日期,\n" +
                " IF(yy.商业险结束日期 IS NOT  NULL ,yy.商业险结束日期 , a.`商业险结束日期`   ) 商业险结束日期," +
                " g.商业险出单日期,\n" +
                " g.交强险出单日期,\n" +
                "    ROUND((IFNULL((SELECT SUM(t.poundage) FROM lsh保险申请表附属_详细 t WHERE t.初始报价单号=g.`初始报价单号`),0)+a.`交强险手续费`),2) AS 交强险手续费 ,  \n" +
                "  ROUND((IFNULL((SELECT SUM(t.poundage) FROM lsh保险申请表附属_详细 t WHERE t.初始报价单号=g.`初始报价单号`),0)+ a.`商业险手续费`),2) AS 商业险手续费 , \n" +
                " g.`登记日期`, \n" +
                " b.logTime as 上次报价日期,\n" +
                "  ( SELECT \n" +
                " SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税' AND bjbx.第N年 = 1 and bjbx.不计免赔 is not NULL ,bjbx.不计免赔,0) \n" +
                "+IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税' AND bjbx.第N年 = 1 and bjbx.折后价 is not NULL ,bjbx.折后价,0)) \n" +
                "FROM  报价保险详细表 bjbx  WHERE     a.保险编号 = bjbx.保险编号  ) 首年商业险  ,\n" +
                " ( SELECT \n" +
                " SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税'   and bjbx.不计免赔 is not NULL ,bjbx.不计免赔,0)) \n" +
                "   +SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税'   and bjbx.折后价 is not NULL ,bjbx.折后价,0))\n" +
                "FROM  报价保险详细表 bjbx  WHERE     a.保险编号 = bjbx.保险编号  ) 一保多年商业险保费  ,\n" +
                " ( SELECT \n" +
                " SUM(if( bjbx.险种 = '交强险' AND bjbx.第N年 = 1,bjbx.折后价,0) )\n" +
                "FROM  报价保险详细表 bjbx  WHERE     a.保险编号 = bjbx.保险编号  ) 交强险  ,\n" +
                " ( SELECT \n" +
                " SUM(if( bjbx.险种 = '车船税' AND bjbx.第N年 = 1,bjbx.折后价,0) )\n" +
                "FROM  报价保险详细表 bjbx  WHERE     a.保险编号 = bjbx.保险编号  ) 车船税  ," +
                " m.报价次数 as 报价次数,\n" +
                " d.车架号 AS VIN码,\n" +
                " e.上次进店日期,\n" +
                " c.预计交车日期,   \n" +
                " c.`初始报价单号`, \n" +
                " g.操作人  ," +
                " xx.`批单类型`, " +
                " 用户.`人员姓名`  as  所属保险专员 FROM  `报价单表` AS c\n" +
                " JOIN  vw_max_quotedcode_all AS x ON x.`报价编号`=c.`报价编号`\n" +
                " JOIN 报价保险表  AS a  ON  a.`保险编号`=c.`保险编号`\n" +
                " JOIN insurance_log AS b ON c.`初始报价单号`=b.orgQsn\n" +
                " JOIN  (SELECT *,MAX(ab.id) AS `保险单`  FROM  insurance_log AS ab   where  ab.orgQsn like  :likePermit    GROUP BY ab.orgQsn ) AS  y ON b.orgQsn=y.orgQsn \n" +
                " LEFT JOIN `lsh库存表`  AS d ON d.`库存编号`=c.`库存编号`\n" +
                " JOIN  `顾客记录汇总` AS e ON e.`顾客编号`=c.`顾客编号`\n" +
                " JOIN  `用户` AS f ON c.`销售顾问`=f.`用户名`\n" +
                " JOIN `lsh保险申请附属` AS g ON g.`初始报价单号`=c.`初始报价单号`\n" +
                " LEFT JOIN  `用户` AS 用户 ON g.`操作人`=用户.`用户名`" +
                " JOIN `lsh车型` AS  h ON c.`车型编号`=h.`车型编号`\n" +
         /*   " LEFT JOIN 报价保险详细表 bjbx on  a.保险编号 = bjbx.保险编号\n" +*/   //做子查询优化
                " JOIN (SELECT sum(if(a.state=01 , 1, 0)) AS 报价次数 ,a.orgQsn FROM insurance_log AS a where a.state<>'00'  AND  A.orgQsn like  :likePermit     GROUP BY a.orgQsn) AS m ON m.orgQsn=b.orgQsn\n" +
                " LEFT JOIN 报价订单表 l ON l.订单编号 = c.订单编号\n" +
                " LEFT JOIN (select MAX(a.id) max_xx_id ,a.初始报价单号 from   lsh保险申请表附属_详细  as a   where a.`类型`=0  AND `初始报价单号` like  :likePermit  GROUP BY   初始报价单号   )  max_xx_id  ON max_xx_id.`初始报价单号`=c.`初始报价单号`\n" +
                " LEFT JOIN (select MAX(a.id) max_yy_id ,a.初始报价单号 from   lsh保险申请表附属_详细  as a   where a.`类型`=1   AND `初始报价单号` like  :likePermit GROUP BY   初始报价单号   )  max_yy_id  ON max_yy_id.`初始报价单号`=c.`初始报价单号`\n" +
                " LEFT JOIN  lsh保险申请表附属_详细  xx ON xx.`id`= max_xx_id.max_xx_id" +
                " LEFT JOIN  lsh保险申请表附属_详细  yy ON yy.`id`= max_yy_id.max_yy_id" +
                " WHERE\n" +
                " b.id=y.`保险单`   AND b.state<>'00'" +
                " AND  c.许可 =:permit  and  ( c.invalid_reason is null or c.invalid_reason <> '01'  ) " ;
              //  " AND ( ISNULL(g.`操作人`) OR g.`操作人` = '' OR g.`操作人` =:liveName )   ";
        Map params = Maps.newHashMap();
        params.put("permit", permit);
       // params.put("liveName", liveName);
        params.put("likePermit", permit+"%");
        if (!Strings.isNullOrEmpty(request.tciNo)) {
            sql += "and a.交强险保单号 like :tciNo \n";
            params.put("tciNo", "%" + request.tciNo + "%");
        }

        if (!Strings.isNullOrEmpty(request.vciNo)) {
            sql += "and a.商业险保单号 like :vciNo \n";
            params.put("vciNo", "%" + request.vciNo + "%");
        }

        if (!Strings.isNullOrEmpty(request.tciCompany)) {
            sql += "and a.交强险保险公司 = :tciCompany \n";
            params.put("tciCompany", request.tciCompany);
        }

        if (!Strings.isNullOrEmpty(request.vciCompany)) {
            sql += "and a.商业险保险公司 = :vciCompany \n";
            params.put("vciCompany", request.vciCompany);
        }

        if (!Strings.isNullOrEmpty(request.salesName)) {
            sql += "and f.人员姓名 like :salesName \n";
            params.put("salesName", "%" + request.salesName + "%");
        }

        if (!Strings.isNullOrEmpty(request.status)) {
            sql += "and b.state = :status \n";
            params.put("status", request.status);
        }

        if (!Strings.isNullOrEmpty(request.username)) {
            sql += "and e.顾客姓名 like :username \n";
            params.put("username", "%" + request.username + "%");
        }

        if (!Strings.isNullOrEmpty(request.userPhone)) {
            sql += "and e.电话号码 like :userPhone \n";
            params.put("userPhone", "%" + request.userPhone + "%");
        }

        if (!Strings.isNullOrEmpty(request.vin)) {
            sql += "and d.生产号 like :vin \n";
            params.put("vin", "%" + request.vin + "%");
        }

        if (!Strings.isNullOrEmpty(request.type)) {
            sql += "and h.车型 like :type \n";
            params.put("type", "%" + request.type + "%");
        }

        if (request.startEnterDate != null) {
            sql += "and e.上次进店日期 >= :startEnterDate \n";
            params.put("startEnterDate", request.startEnterDate);
        }

        if (request.endEnterDate != null) {
            sql += "and e.上次进店日期 <= :endEnterDate \n";
            params.put("endEnterDate", request.endEnterDate);
        }
        if (request.startOrderDate != null){
            sql += "and (g.交强险出单日期 >= :startOrderDate or g.商业险出单日期 >= :startOrderDate)\n";
            params.put("startOrderDate", request.startOrderDate);
        }
        if (request.endOrderDate != null){
            sql += "and (g.交强险出单日期 <= :endOrderDate or g.商业险出单日期 <= :endOrderDate)\n";
            params.put("endOrderDate", request.endOrderDate);
        }
        if (request.orderRelyType != null) {
            sql += "and xx.`批单类型`= :orderRelyType\n";
            params.put("orderRelyType", request.orderRelyType);
        }
        if (!StringUtils.isNullOrBlank(request.forceInsuranceStartDate)) {
            sql += "and g.交强险出单日期 >= :forceInsuranceStartDate ";
            params.put("forceInsuranceStartDate",request.forceInsuranceStartDate);
        }
        if (!StringUtils.isNullOrBlank(request.forceInsuranceEndDate)) {
            sql += "and g.交强险出单日期 <= :forceInsuranceEndDate ";
            params.put("forceInsuranceEndDate",request.forceInsuranceEndDate);
        }

        if (!StringUtils.isNullOrBlank(request.commercialInsuranceStartDate)) {
            sql += "and g.商业险出单日期 >= :commercialInsuranceStartDate ";
            params.put("commercialInsuranceStartDate",request.commercialInsuranceStartDate);
        }
        if (!StringUtils.isNullOrBlank(request.commercialInsuranceEndDate)) {
            sql += "and g.商业险出单日期 <= :commercialInsuranceEndDate ";
            params.put("commercialInsuranceEndDate",request.commercialInsuranceEndDate);
        }
        sql += "group by c.`初始报价单号`";
        sql += "order by g.操作人 ,b.logTime desc  ";

        return this.getPage(sql, params, pageSize, pageIndex);
    }

    @Override
    public PageData getInsuranceAccountInfo(String liveName, int pageSize, int pageIndex, String permit, String tciNo, String vciNo, String tciCompany, String vciCompany, String salesName, String status, String username, String userPhone, String vin, String type, Date startEnterDate, Date endEnterDate, Date startDueDate, Date endDueDate, Date startOrderDate, Date endOrderDate, Date startExpectInvoiceDate, Date endExpectInvoiceDate, String orderRelyType,
                                            String forceInsuranceStartDate,String forceInsuranceEndDate,String commercialInsuranceStartDate,String commercialInsuranceEndDate) {
        String sql = " SELECT  bjbx.`第N年`,\n" +
                " a.保险编号,\n" +
                " a.投保类型,\n" +
                " b.state as 保险销售状态,\n" +
                " e.顾客姓名 as 客户姓名,\n" +
                " e.电话号码 as 客户电话,\n" +
                " f.人员姓名 as 销售顾问,\n" +
                " h.级别 as 意向车系,\n" +
                " h.车型 as 意向车型,\n" +
                " h.车型详细 as  意向车款,                 \n" +
                " a.交强险保险公司 as 交强险保险公司,                \n" +
                " a.商业险保险公司 as 商业险保险公司,\n" +
                " xx.商业险保单号 as 商业险保单号, \n" +  //改动
                " xx.交强险保单号 as 交强险保单号, \n" +//改动
                " IF(xx.交强险开始日期 IS NOT  NULL ,xx.交强险开始日期 , a.`交强险开始日期` ) 交强险开始日期,\n" +
                " IF(xx.交强险结束日期 IS NOT  NULL ,xx.交强险结束日期 , a.`交强险结束日期`   ) 交强险结束日期,\n" +
                " IF(yy.商业险开始日期 IS NOT  NULL ,yy.商业险开始日期 ,  a.`商业险开始日期`  ) 商业险开始日期,\n" +
                " IF(yy.商业险结束日期 IS NOT  NULL ,yy.商业险结束日期 , a.`商业险结束日期`   ) 商业险结束日期,\n" +
                " g.商业险出单日期,\n" +
                " g.交强险出单日期,\n" +
                " g.`登记日期`, " +
                " a.`交强险手续费`,\n" +
                "a.`商业险手续费`, \n " +


               /* "--   1.车辆损失险/保额    车辆损失险/保费    车辆损失险/不计免赔\n" +*/
                " SUM(  IF(bjbx.险种 = '车辆损失险', bjbx.参数, 0) ) AS `车辆损失险/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '车辆损失险', bjbx.折后价, 0) ) AS  `车辆损失险/保费`,\n" +
                " SUM(  IF(bjbx.险种 = '车辆损失险', bjbx.不计免赔, 0) ) AS  `车辆损失险/不计免赔`,\n" +

               /*  "--2. 商业三者责任险/保额  商业三者责任险/保费  商业三者责任险/不计免赔\n" +*/
                " SUM(  IF(bjbx.险种 = '商业三者责任险', bjbx.参数, 0) )  AS `商业三者责任险/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '商业三者责任险', bjbx.折后价, 0) )  AS `商业三者责任险/保费`,\n" +
                " SUM(  IF(bjbx.险种 = '商业三者责任险', bjbx.不计免赔, 0) ) AS `商业三者责任险/不计免赔`,\n" +

                /* "-- 3.全车盗抢险/保费  全车盗抢险/不计免赔 \n" +*/
                " SUM(  IF(bjbx.险种 = '全车盗抢险', bjbx.折后价, 0) )  AS `全车盗抢险/保费`,\n" +
                " SUM(  IF(bjbx.险种 = '全车盗抢险', bjbx.不计免赔, 0) )  AS `全车盗抢险/不计免赔`,\n" +

                /* "-- 4. 车上人员责任险-司机/保额  车上人员责任险-司机/保费  车上人员责任险-司机/不计免赔\n" +*/
                " SUM(  IF(bjbx.险种 = '车上人员责任险 - 司机', bjbx.参数, 0) )  AS  `车上人员责任险-司机/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '车上人员责任险 - 司机', bjbx.折后价, 0) ) AS  `车上人员责任险-司机/保费`,\n" +
                " SUM(  IF(bjbx.险种 = '车上人员责任险 - 司机', bjbx.不计免赔, 0) ) AS `车上人员责任险-司机/不计免赔`,\n" +

               /*  "-- 5. 车上人员责任险-乘客/保额  车上人员责任险-乘客/保费  车上人员责任险-乘客/不计免赔\n" +*/
                " SUM(  IF(bjbx.险种 = '车上人员责任险 - 乘客', bjbx.参数, 0) ) AS  `车上人员责任险-乘客/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '车上人员责任险 - 乘客', bjbx.折后价, 0) ) AS `车上人员责任险-乘客/保费`,\n" +
                " SUM(  IF(bjbx.险种 = '车上人员责任险 - 乘客', bjbx.不计免赔, 0) ) AS  `车上人员责任险-乘客/不计免赔`,\n" +

               /*  "-- 6.玻璃单独破碎险/保额  玻璃单独破碎险/保费 \n" +*/
                " SUM(  IF(bjbx.险种 = '玻璃单独破碎险', bjbx.参数, 0) ) AS `玻璃单独破碎险/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '玻璃单独破碎险', bjbx.折后价, 0) ) AS `玻璃单独破碎险/保费`,\n" +

               /*  "-- 7.车身划痕损失险/保额  车身划痕损失险/保费  车身划痕损失险/不计免赔\n" +*/
                " SUM(  IF(bjbx.险种 = '车身划痕损失险', bjbx.参数, 0) )  AS `车身划痕损失险/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '车身划痕损失险', bjbx.折后价, 0) )  AS `车身划痕损失险/保费`,\n" +
                " SUM(  IF(bjbx.险种 = '车身划痕损失险', bjbx.不计免赔, 0) ) AS  `车身划痕损失险/不计免赔`,\n" +

               /*  "-- 8.自燃损失险/保费 \n" +*/
                " SUM(  IF(bjbx.险种 = '自燃损失险', bjbx.折后价, 0) ) AS  `自燃损失险/保费` ,\n" +

               /*  "-- 9.发动机涉水损失险/保费 \n" +*/
                " SUM(  IF(bjbx.险种 = '发动机涉水损失险', bjbx.折后价, 0) ) AS `发动机涉水损失险/保费` ,\n" +

               /*  "--  10. 倒车镜、车灯单独损失险/保费 \n" +*/
                " SUM(  IF(bjbx.险种 = '倒车镜、车灯单独损失险', bjbx.折后价, 0) ) AS `倒车镜、车灯单独损失险/保费` ,\n" +

               /* "--  11.新增设备损失险/保额  新增设备损失险/保费\n" +*/
                " SUM(  IF(bjbx.险种 = '新增设备损失险', bjbx.参数, 0) ) AS `新增设备损失险/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '新增设备损失险', bjbx.折后价, 0) ) AS `新增设备损失险/保费`,\n" +

               /*  "--  12.指定修理厂险/保费 \n" +*/
                " SUM(  IF(bjbx.险种 = '指定修理厂险', bjbx.折后价, 0) ) AS `指定修理厂险/保费`,\n" +

               /*  "--  13.修理期间费用补偿险/保额     修理期间费用补偿险/保费\n" +*/
                " SUM(  IF(bjbx.险种 = '修理期间费用补偿险', bjbx.参数, 0) ) AS `修理期间费用补偿险/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '修理期间费用补偿险', bjbx.折后价, 0) ) AS `修理期间费用补偿险/保费` ,\n" +

               /*  "--  14.车上货物责任险/保额  车上货物责任险/保费\n" +*/
                " SUM(  IF(bjbx.险种 = '车上货物责任险', bjbx.参数, 0) ) AS `车上货物责任险/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '车上货物责任险', bjbx.折后价, 0) )  AS `车上货物责任险/保费` ,\n" +

               /*  "-- 15.精神损害抚慰金责任险/保额  精神损害抚慰金责任险/保费\n" +*/
                " SUM(  IF(bjbx.险种 = '精神损害抚慰金责任险', bjbx.参数, 0) ) AS `精神损害抚慰金责任险/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '精神损害抚慰金责任险', bjbx.折后价, 0) ) AS `精神损害抚慰金责任险/保费` ,\n" +

               /* "-- 16.机动车损失保险无法找到第三方特约险/保费\n" +*/
                " SUM(  IF(bjbx.险种 = '机动车损失保险无法找到第三方特约险', bjbx.折后价, 0) ) AS `机动车损失保险无法找到第三方特约险/保费` ,\n" +

               /*  "-- 17. 自定义险种一/保额  自定义险种一/保费\t\n" +*/
                " SUM(  IF(bjbx.险种 = '自定义险种一', bjbx.参数, 0) ) AS `自定义险种一/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '自定义险种一', bjbx.折后价, 0) ) AS `自定义险种一/保费` ,\n" +

               /*  "-- 18.自定义险种二/保额  自定义险种二/保费  附加险/不计免赔\n" +*/
                " SUM(  IF(bjbx.险种 = '自定义险种二', bjbx.参数, 0) ) AS  `自定义险种二/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '自定义险种二', bjbx.折后价, 0) ) AS `自定义险种二/保费`,\n" +
                " SUM(  IF(bjbx.险种 = '附加险', bjbx.不计免赔, 0) ) AS `附加险/不计免赔`," +

                " b.logTime as 上次报价日期,\n" +
                " SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税' AND bjbx.第N年 = 1 and bjbx.不计免赔 is not NULL ,bjbx.不计免赔,0)) \n" +
                " +SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税' AND bjbx.第N年 = 1 and bjbx.折后价 is not NULL ,bjbx.折后价,0))  首年商业险,\n" +
                " SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税'   and bjbx.不计免赔 is not NULL ,bjbx.不计免赔,0)) \n" +
                " +SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税'   and bjbx.折后价 is not NULL ,bjbx.折后价,0))  一保多年商业险保费,\n" +
                " SUM(if( bjbx.险种 = '交强险' AND bjbx.第N年 = 1,bjbx.折后价,0) ) 交强险,\n" +
                " SUM(if( bjbx.险种 = '车船税' AND bjbx.第N年 = 1,bjbx.折后价,0) ) 车船税,\n" +
                " m.报价次数 as 报价次数,\n" +
                " d.车架号 AS VIN码,\n" +
                " e.上次进店日期,\n" +
                " c.预计交车日期,   \n" +
                " c.`初始报价单号`, \n" +
                " g.操作人  ,  \n" +
                " xx.`批单类型`,  \n" +
                " 用户.`人员姓名`  as  所属保险专员 \n" +
                " FROM  `报价单表` AS c\n" +
                " JOIN  vw_max_quotedcode_all AS x ON x.`报价编号`=c.`报价编号`\n" +
                " JOIN 报价保险表  AS a  ON  a.`保险编号`=c.`保险编号`\n" +
                " JOIN insurance_log AS b ON c.`初始报价单号`=b.orgQsn\n" +
                " JOIN  (SELECT *,MAX(ab.id) AS `保险单`  FROM  insurance_log AS ab  GROUP BY ab.orgQsn ) AS  y ON b.orgQsn=y.orgQsn \n" +
                " LEFT JOIN `lsh库存表`  AS d ON d.`库存编号`=c.`库存编号`\n" +
                " JOIN  `顾客记录汇总` AS e ON e.`顾客编号`=c.`顾客编号`\n" +
                " JOIN  `用户` AS f ON c.`销售顾问`=f.`用户名`\n" +
                " JOIN `lsh保险申请附属` AS g ON g.`初始报价单号`=c.`初始报价单号`\n" +
                " LEFT JOIN  `用户` AS 用户 ON g.`操作人`=用户.`用户名`  \n" +
                " JOIN `lsh车型` AS  h ON c.`车型编号`=h.`车型编号`\n" +
                "   JOIN 报价保险详细表 bjbx on  a.保险编号 = bjbx.保险编号\n" +
                " JOIN (SELECT sum(if(a.state=01 , 1, 0)) AS 报价次数 ,a.orgQsn FROM insurance_log AS a where a.state<>'00'  GROUP BY a.orgQsn) AS m ON m.orgQsn=b.orgQsn\n" +
                " LEFT JOIN 报价订单表 l ON l.订单编号 = c.订单编号\n" +
                " LEFT JOIN (select MAX(a.id) max_xx_id ,a.初始报价单号 from   lsh保险申请表附属_详细  as a GROUP BY   初始报价单号, 第N年     )  max_xx_id  ON max_xx_id.`初始报价单号`=c.`初始报价单号`\n" +
                " LEFT JOIN  lsh保险申请表附属_详细  xx ON xx.`id`= max_xx_id.max_xx_id  and xx.`第N年`=bjbx.`第N年`\n" +
                " LEFT JOIN (select MAX(a.id) max_yy_id ,a.初始报价单号 from   lsh保险申请表附属_详细  as a   where a.`类型`=1  GROUP BY   初始报价单号   )  max_yy_id  ON max_yy_id.`初始报价单号`=c.`初始报价单号`\n" +
                " LEFT JOIN  lsh保险申请表附属_详细  yy ON yy.`id`= max_yy_id.max_yy_id" +
                " WHERE\n" +
                " b.id=y.`保险单`   AND b.state<>'00'  \n" +
                " AND  c.许可 =:permit  AND c.`是否驳回`<>'是'    ";
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        if (!Strings.isNullOrEmpty(tciNo)) {
            sql += " and xx.交强险保单号 like :tciNo \n";
            params.put("tciNo", "%" + tciNo + "%");
        }

        if (!Strings.isNullOrEmpty(vciNo)) {
            sql += " and xx.商业险保单号 like :vciNo \n";
            params.put("vciNo", "%" + vciNo + "%");
        }

        if (!Strings.isNullOrEmpty(tciCompany)) {
            sql += " and a.交强险保险公司 = :tciCompany \n";
            params.put("tciCompany", tciCompany);
        }

        if (!Strings.isNullOrEmpty(vciCompany)) {
            sql += " and a.商业险保险公司 = :vciCompany \n";
            params.put("vciCompany", vciCompany);
        }

        if (!Strings.isNullOrEmpty(salesName)) {
            sql += " and f.人员姓名 like :salesName \n";
            params.put("salesName", "%" + salesName + "%");
        }

        if (!Strings.isNullOrEmpty(status)) {
            sql += " and b.state = :status \n";
            params.put("status", status);
        }

        if (!Strings.isNullOrEmpty(username)) {
            sql += " and e.顾客姓名 like :username \n";
            params.put("username", "%" + username + "%");
        }

        if (!Strings.isNullOrEmpty(userPhone)) {
            sql += " and e.电话号码 like :userPhone \n";
            params.put("userPhone", "%" + userPhone + "%");
        }
        if (!Strings.isNullOrEmpty(type)) {
            sql += " and h.车型 like :type \n";
            params.put("type", "%" + type + "%");
        }
        if (startOrderDate != null){
            sql += " and (g.交强险出单日期 >= :startOrderDate or g.商业险出单日期 >= :startOrderDate)\n";
            params.put("startOrderDate", startOrderDate);
        }
        if (endOrderDate != null){
            sql += " and (g.交强险出单日期 <= :endOrderDate or g.商业险出单日期 <= :endOrderDate)\n";
            params.put("endOrderDate", endOrderDate);
        }
        if (startExpectInvoiceDate != null){
            sql += " and l.预计开票日期 >= :startExpectInvoiceDate\n";
            params.put("startExpectInvoiceDate", startExpectInvoiceDate);
        }
        if (endExpectInvoiceDate != null){
            sql += " and l.预计开票日期 <= :endExpectInvoiceDate\n";
            params.put("endExpectInvoiceDate", endExpectInvoiceDate);
        }

        if (!Strings.isNullOrEmpty(orderRelyType)) {
            sql += " and xx.`批单类型`= :orderRelyType\n";
            params.put("orderRelyType", orderRelyType);
        }
        if (!StringUtils.isNullOrBlank(forceInsuranceStartDate)) {
            sql += "and g.交强险出单日期 >= :forceInsuranceStartDate ";
            params.put("forceInsuranceStartDate",forceInsuranceStartDate);
        }
        if (!StringUtils.isNullOrBlank(forceInsuranceEndDate)) {
            sql += "and g.交强险出单日期 <= :forceInsuranceEndDate ";
            params.put("forceInsuranceEndDate",forceInsuranceEndDate);
        }

        if (!StringUtils.isNullOrBlank(commercialInsuranceStartDate)) {
            sql += "and g.商业险出单日期 >= :commercialInsuranceStartDate ";
            params.put("commercialInsuranceStartDate",commercialInsuranceStartDate);
        }
        if (!StringUtils.isNullOrBlank(commercialInsuranceEndDate)) {
            sql += "and g.商业险出单日期 <= :commercialInsuranceEndDate ";
            params.put("commercialInsuranceEndDate",commercialInsuranceEndDate);
        }
        sql += "group by c.`初始报价单号`  , bjbx.`第N年`  ";
        sql += "order by    g.操作人 ,  b.logTime desc  ";

        return this.getPage(sql, params, pageSize, pageIndex);
    }

    // 保险主界面双击
    @Override
    public Object getInsuranceInfoByCode(String permit,int code) {
        String sql = " " +
                " SELECT \n" +
                " bjbx.保险编号,\n" +
                " gkjl.顾客姓名,\n" +
                " gkjl.顾客性别,\n" +
                " gkjl.电话号码,\n" +
                " bjd.金融,\n" +
                " bjd.车型编号,\n" +
                " bjbx.贷款类型,\n" +
                " bjd.原价,\n" +
                // Bug4059，原来取的车型表的价钱，原因不明，现在改为从报价单取数
                " bjd.折后价, \n" +
                " yh.人员姓名,\n" +
                " yh.人员电话,\n" +
                " gkjl.`新车销售状态`,\n" +
                " bjd.当前状态 ,\n" +
                " bxsq.state AS '新保销售状态',   \n" +
                " y.报价次数 as 报价次数,##未判断\n" +
                " bjd.预计交车日期,\n" +
                " bjddb.预计开票日期,\n" +
                " kc.车架号 AS VIN码,\n" +
                " bjbx.一保N年,\n " +
                " bjd.`初始报价单号`\n " +
                " FROM 报价保险表 bjbx\n" +
                " JOIN 报价单表 bjd ON bjbx.保险编号 = bjd.保险编号\n" +
                " JOIN  vw_max_quotedcode_all AS x ON x.`报价编号`=bjd.`报价编号` \n" +
                " JOIN insurance_log bxsq ON bjd.`初始报价单号` = bxsq.orgQsn\n " +
                " JOIN  (SELECT *,MAX(ab.id) AS `保险单`,sum(IF(ab.state = 01, 1, 0)) AS 报价次数  FROM  insurance_log AS ab " +
                " WHERE ab.orgQsn LIKE CONCAT(:permit,'%') " +
                " GROUP BY ab.orgQsn ) AS  y ON bxsq.orgQsn=y.orgQsn " +
                " JOIN 顾客记录汇总 gkjl ON bjd.顾客编号 = gkjl.顾客编号\n" +
                " JOIN 用户 yh ON  bjd.`销售顾问` = yh.用户名\n" +
                " JOIN LSH车型 cx ON bjd.车型编号 = cx.车型编号\n" +
                "  LEFT JOIN `lsh库存表`  AS kc ON kc.`库存编号`=bjd.`库存编号`\n" +
                "LEFT join 报价订单表 bjddb on bjddb.订单编号 = bjd.订单编号\n" +
                " where   bjbx.保险编号 =:code \n" +
                " AND  bxsq.id=y.`保险单` \n ";
        Map params = Maps.newHashMap();
        params.put("code", code);
        params.put("permit", permit);
        List data = this.getList(sql, params);
        return Iterables.getFirst(data, null);
    }


    @Override
    public Object getInsuranceOffersById(int id) {
        String sql = " select \n" +
                " a.交强险保险公司,\n" +
                " a.交强险开始日期,\n" +
                " a.交强险结束日期,\n" +
                " a.商业险保险公司,\n" +
                " a.商业险开始日期,\n" +
                " a.商业险结束日期, \n" +
                " c.交强险预计出单日期,\n" +
                " c.交强险出单日期,\n" +
                " m.交强险投保期,\n" +
                " c.商业险预计出单日期,\n" +
                " c.商业险出单日期,\n" +
                " a.更新时间,\n" +
                " a.一保N年,\n" +
                "\n" +
                " SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税' AND bjbx.第N年 = 1 and bjbx.不计免赔 is not NULL ,bjbx.不计免赔,0)) + \n" +
                " SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税' AND bjbx.第N年 = 1 and bjbx.折后价 is not NULL ,bjbx.折后价,0))  首年商业险, \n" +
                " SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税'   and bjbx.不计免赔 is not NULL ,bjbx.不计免赔,0)) + \n" +
                " SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税'   and bjbx.折后价 is not NULL ,bjbx.折后价,0))  一保多年商业险保费, \n" +
                " if( bjbx.险种 = '交强险' AND bjbx.第N年 = 1,bjbx.折后价,0)  交强险,\n" +
                " if( bjbx.险种 = '车船税' AND bjbx.第N年 = 1,bjbx.折后价,0)  车船税  \n" +
                " from \n" +
                " `报价保险表` a\n" +
                " LEFT JOIN   lsh保险申请附属 c on c.`初始报价单号` = (SELECT DISTINCT 初始报价单号 FROM 报价单表 quote WHERE quote.保险编号=a.保险编号 limit 1 )  \n" +
                " LEFT JOIN `报价保险详细表` as bjbx  on a.`保险编号`=bjbx.`保险编号`\n" +
                " LEFT JOIN `报价保险表` as m  on a.`保险编号`= m.`保险编号`\n" +
                " where   a.`保险编号`=:code \n" +
                " GROUP BY bjbx.`保险编号`;";
        Map params = Maps.newHashMap();
        params.put("code", id);
        List data = this.getList(sql, params);
        return Iterables.getFirst(data, null);
    }

    @Override
    public List getInsuranceCompanyList(String permit) {
        String sql = "select a.*,b.id 区域编号,IFNULL(b.dealer_name,'全部') 区域名称 ,b.permit 区域许可 from LSH保险公司设置 a left join group_area b on a.area_id = b.id order by a.更新时间 desc ";
        List data = this.getList(sql);
        return data;
    }

    @Override
    public List getInsuranceCompanyListByLicense(String license) {
        String sql = "select " +
                "	sz.保险公司编号, " +
                "    sz.保险公司, " +
                "    sz.状态, " +
                "    case sz.状态 " +
                "		when '必选' then sz.启用状态 " +
                "		when '可选' then sy.使用状态 " +
                "    end as 本店状态 " +
                "	, " +
                "sz.`启用状态` 集团状态," +
                "sz.创建时间, " +
                "    sy.更新时间, " +
                "    sy.更新时间 as 使用更新时间, " +
                "    sy.更新账号, " +
                "    sxf.交强险手续费, " +
                "    sxf.商业险手续费, " +
                "    sxf.客户类别, " +
                "    sxf.备注 " +
                "from LSH保险公司设置 sz " +
                " join LSH保险手续费 sxf on sz.保险公司编号 = sxf.保险公司编号 " +
                " join LSH保险公司使用 sy on sz.保险公司编号 = sy.保险公司编号 and sxf.许可 = sy.许可 " +
                "and sy.许可 = :license ";
        Map params = Maps.newHashMap();
        params.put("license", license);
        List data = this.getList(sql, params);
        return data;
    }

    @Override
    public PageData getSuggestPlan(String permit, int pageSize, int pageIndex) {
        String sql = "select " +
                "   aa.`险种编号`, " +
                "	if(aa.来源='PbP', CONCAT('PbP ', aa.级别 ), aa.级别 ) 级别, " +
                "	aa.车型," +
                "   aa.品牌, " +
                "	aa.座位数, " +
                "	aa.车型平均价格, " +
                "	aa.车船税, " +
                "	ROUND(  concat(bb.交强险 + cc.商业险, ''),2) as 保险总价, " +
                "	bb.交强险保险公司, " +
                "	bb.交强险保险公司编号, " +
                "	bb.交强险, " +
                "	aa.商业险保险公司, " +
                "	aa.商业险保险公司编号, " +
                "	cc.商业险, " +
                "	aa.车型编号, " +
                "   aa.许可, " +
                "   aa.保险方案备注 " +
                "from ( " +
                "	select " +
                "       c.品牌, " +
                "      a.`险种编号`, " +
                "		c.级别, " +
                "		c.车型, " +
                "       c.来源, " +
                "		c.座位数, " +
                "		a.车型平均价格, " +
                "		a.参数 as 车船税, " +
                "		a.车型编号, " +
                "       b.保险公司 as 商业险保险公司, " +
                "       a.保险公司编号 as 商业险保险公司编号, " +
                "       a.许可, " +
                "       a.保险方案备注 " +
                "	from LSH保险设置 a " +
                "	join LSH车型 c on a.车型编号 = c.车型编号 " +
                "	join LSH保险公司设置 b on a.保险公司编号 = b.保险公司编号 " +
                "	where 险种 = '车船税' " +
                "   and a.许可 = :permit " +
                ") aa " +
                "join ( " +
                "	select distinct " +
                "		a.险种编号, " +
                "		b.保险公司 as 交强险保险公司, " +
                "		a.参数 as 交强险, " +
                "		a.车型编号, " +
                "		a.保险公司编号 as 交强险保险公司编号, " +
                "		a.许可 " +
                "	from LSH保险设置 a " +
                "	join LSH保险公司设置 b on a.保险公司编号 = b.保险公司编号 " +
                "	where 险种 = '交强险' " +
                ") bb on aa.车型编号 = bb.车型编号 and aa.许可 = bb.许可 " +
                "join ( " +
                "	select sum(ifnull(a.保费,0)+ ifnull(a.不计免赔,0)) as 商业险, " +
                "		a.车型编号, " +
                "		a.保险公司编号, " +
                "		a.许可 " +
                "	from LSH保险设置 a " +
                "	where 险种 <> '交强险' " +
                "	and 险种 <> '车船税' " +
                "	group by a.车型编号, a.许可 " +
                ") cc on aa.车型编号 = cc.车型编号 and aa.许可 = cc.许可 order by aa.级别,车型";
        Map<String, String> params = new HashMap<>();
        params.put("permit", permit);
        PageData page = this.getPage(sql, params, pageSize, pageIndex);
        return page;
    }

    @Override
    public List getDetailsByPlan(String permit, int carId) {
        String sql = "select " +
                "	sz.险种编号, " +
                "	sz.险种, " +
                "	sz.参数, " +
                "	sz.不计免赔, " +
                "	sz.保费, " +
                "	c.保险公司编号, " +
                "	c.保险公司, " +
                "	sz.车型编号 " +
                "from LSH保险设置 sz " +
                "LEFT JOIN LSH保险公司设置 c on sz.保险公司编号 = c.保险公司编号 " +
                "where sz.车型编号 = :id and sz.`许可`=:permit";
        Map params = Maps.newHashMap();
        params.put("id", carId);
        params.put("permit", permit);
        return this.getList(sql, params);
    }

    @Override
    public List getFailReasonList() {
        String sql = "select yy.`新保战败原因编号`, ifnull(zzzyy.`战败内容`, '停用'), ifnull(zzyy.`战败内容`, '停用')" +
                ", ifnull(zzzyy.`战败内容`, '停用') as 保险准战败原因状态, ifnull(zzyy.`战败内容`, '停用') as 保险战败原因状态" +
                ", yy.`战败原因`, yy.`创建日期`, yy.`更新时间`, yy.`启用状态`\n" +
                "from LSH保险战败原因 as yy\n" +
                "left join LSH保险战败原因附属 zzzyy on zzzyy.`保险战败原因编号` = yy.`新保战败原因编号` and zzzyy.`战败分类` = '保险准战败原因'\n" +
                "left join LSH保险战败原因附属 zzyy on zzyy.`保险战败原因编号` = yy.`新保战败原因编号` and zzyy.`战败分类` = '保险战败原因'";
        return this.getList(sql);
    }

    @Override
    public Object getFailReasonByInsuranceCode(String code) {
        String sql = "select \n" +
                "IF (a.`战败备注`<>'', a.`战败备注`, b.`战败原因` ) AS 新保战败原因\n" +
                " from  lsh保险申请附属  as a\n" +
                "LEFT  JOIN `lsh保险战败原因` AS b ON a.`准战败原因`=b.`新保战败原因编号`\n" +
                "LEFT JOIN `lsh保险战败原因附属` AS c ON b.`新保战败原因编号` = c.`保险战败原因编号` AND c.`战败分类` = '保险准战败'\n" +
                "WHERE a.`初始报价单号`=:code";
        Map params = Maps.newHashMap();
        params.put("code", code);
        List data = this.getList(sql, params);
        return Iterables.getFirst(data, null);
    }

    @Override
    public Object getPreFailReasonByInsuranceCode(String code, String insId) {
        String sql = "SELECT * FROM \n" +
                "\t( SELECT MAX(a.`第N年`) `第N年`, b.`折后价` FROM `报价保险详细表` AS a LEFT JOIN `报价保险详细表` AS b" +
                " ON ( b.`保险编号` =:insId AND b.`第N年` = '1' AND b.`险种` = '车船税' ) WHERE a.`保险编号` =:insId ) a\n" +
                "\tLEFT JOIN \n" +
                "\t(SELECT IF ( a.`准战败备注` <> '', a.`准战败备注`, b.`战败原因` ) AS 新保准战败原因 FROM lsh保险申请附属 AS a " +
                "LEFT JOIN `lsh保险战败原因` AS b ON a.`准战败原因` = b.`新保战败原因编号` \n" +
                "\t\tLEFT JOIN `lsh保险战败原因附属` AS c ON b.`新保战败原因编号` = c.`保险战败原因编号` AND c.`战败分类` = '保险准战败' " +
                "WHERE a.`初始报价单号` =:code limit 1) b ON 1=1";
        Map params = Maps.newHashMap();
        params.put("code", code);
        params.put("insId", insId);
        List data = this.getList(sql, params);
        return Iterables.getFirst(data, null);
    }

    @Override
    public List getComboList(String permit,String brand) {
        StringBuilder sql = new StringBuilder();
        sql.append(
                "select distinct  " +
                "	a.保险公司编号, " +
                "	b.保险公司 " +
                "from LSH保险公司使用 a, LSH保险公司设置 b, lsh保险手续费 c  " +
                "where a.许可=:permit AND  LOCATE(b.brand,:brand) > 0 and a.`使用状态` = '启用' and a.保险公司编号 = b.保险公司编号 and a.保险公司编号 = c.保险公司编号 and  c.交强险手续费 > 0 and c.商业险手续费 > 0"
        );
        Map<String, String> params = new HashMap<>();
        params.put("permit", permit);
        params.put("brand", brand);
        if(logger.isDebugEnabled()){
            logger.debug("sql={}. params={}", sql.toString(), JsonObjectUtils.writeAsJson(params));
        }
        return this.getList(sql.toString(), params);
    }
    public List getlastUpdateTime(){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT 更新时间  FROM   `lsh保险公司使用` ORDER BY 更新时间 DESC  LIMIT 1");
        return this.getList(sql.toString());


    }

    @Override
    public List getDlr() {
        String sql = "select 许可_BMBS from LSH经销商表 where 是否测试 = '否'";
        return this.getList(sql);
    }


    @Override
    public List getInsComList(){
            StringBuffer sql=new  StringBuffer();
        sql.append(
                "SELECT * FROM LSH保险公司设置" );
        return  this.getList(sql.toString());
    }

    @Override//查询启用的
    public List getInsComListOpen(){
        StringBuffer sql=new  StringBuffer();
        sql.append(
                "SELECT `保险公司编号`,`保险公司` FROM LSH保险公司设置 where `启用状态`='启用'" );
        return  this.getList(sql.toString());
    }

    @Override//查询大区
    public List getAreaList(){
        StringBuffer sql=new  StringBuffer();
        sql.append(
                        "select  a.`大区`, " +
                        " a.大区_EN `统计单元`," +
                        "  COUNT(*) `经销商总数`  " +
                        " from `lsh经销商表`  as a  " +
                                "where  a.`大区` is not null " +
                        " GROUP BY a.`大区` " );
        return  this.getList(sql.toString());
    }
    @Override//查询小区
    public List getAreasList(){
        StringBuffer sql=new  StringBuffer();
        sql.append(
                "select  a.`小区`, " +
                        " a.小区_EN `统计单元`," +
                        "  COUNT(*) `经销商总数`  " +
                        " from `lsh经销商表`  as a   where  a.`小区`<>'' and a.小区_EN IS NOT NULL  AND  a.小区_EN  <>''  " +
                        " GROUP BY a.`小区` " );
        return  this.getList(sql.toString());
    }

    @Override
    public List getCoInsCompanyList(String rowName,Integer id,String areaName ){
        //查询 保险公司对应大区的
        String sql="  select COUNT(*) `"+rowName+"`  \n" +
                "from LSH保险公司使用 as a\n" +
                "JOIN (select `许可_BMBS` from lsh经销商表 where 大区=:areaName ) as b \n" +
                " on    a.`许可`=b.`许可_BMBS`  \n" +
                "where   a.`保险公司编号`=:id  and `使用状态` ='启用' ";
        Map params = new HashMap<>();
        params.put("id",id.toString());//保险公司编号
        params.put("areaName",areaName);//大区名
        return  this.getList(sql,params);

    }
    @Override
    public List getCoInsCompanysList(String rowName,Integer id,String areaName ){
        //查询 保险公司对应小区的
        String sql="  select COUNT(*) `"+rowName+"`  \n" +
                "from LSH保险公司使用 as a\n" +
                "JOIN (select `许可_BMBS` from lsh经销商表 where 小区=:areaName ) as b \n" +
                " on    a.`许可`=b.`许可_BMBS`  \n" +
                "where   a.`保险公司编号`=:id  ";
        Map params = new HashMap<>();
        params.put("id",id.toString());//保险公司编号
        params.put("areaName",areaName);//大区名
        return  this.getList(sql,params);

    }

    @Override
    public  PageData getDealerInsList(RequestBase requestBase){
        StringBuffer sql= new StringBuffer();
        sql.append(" SELECT  " +
                " a.`许可_BMBS`  经销商代码, " +
                " a.`经销商名`  经销商 , " +
                " a.`大区`, " +
                " a.`省份`, " +
                " a.`城市`, " +
                " a.CTA ");
        String sql1="SELECT `保险公司编号`,`保险公司` FROM LSH保险公司设置";
        List request =this.getList(sql1);
        for (Object insurance:request ){
            String name=((Map)insurance).get("保险公司").toString();
            sql.append( " ,MAX(  CASE   WHEN c.`保险公司`='"+name+"'   and b.`使用状态`='启用'  THEN '1' ELSE  '0'  END ) AS '"+name+"'  ");
        }
        sql.append(" FROM  " +
                " `lsh经销商表`   AS a " +
                " JOIN  `lsh保险公司使用` AS b ON a.`许可_BMBS` =b.`许可` " +
                " JOIN   `lsh保险公司设置`  AS C   ON b.`保险公司编号`=c.`保险公司编号` " +
                "  GROUP BY  a.`许可_BMBS` ");
        return  this.getPage(sql.toString(),requestBase.getPageSize(),requestBase.getPageIndex());
    }
    @Override
    public  List getInsFailList(){
        StringBuffer sql= new StringBuffer("  SELECT a.`新保战败原因编号` '保险战败原因编号', " +
                "  a.`更新账号` '更新账号', " +
                "MAX( CASE   WHEN b.`战败分类`='保险准战败'   THEN 'Y' ELSE  'N'  END ) AS '保险准战败原因' , " +
                "MAX( CASE   WHEN b.`战败分类`='保险战败'   THEN 'Y' ELSE  'N'  END ) AS '保险战败原因' ,   " +
                "a.`战败原因`  ,a.`创建日期` '  创建时间' , " +
                "a.`更新时间` ,a.`启用状态` '启用' " +
                " FROM `lsh保险战败原因` AS  a " +
                "JOIN `lsh保险战败原因附属` AS b ON a.`新保战败原因编号` =b.`保险战败原因编号`  " +
                "group  by a.`新保战败原因编号` " +
                " ORDER BY a.`更新时间` ");
        return  this.getList(sql.toString());
    }

    @Override
    public  List getRealInsFailList(String reason){
            StringBuffer sql=new StringBuffer(" \n" +
                    "select a.`战败原因` ,a.`新保战败原因编号` \n" +
                    "FROM   `lsh保险战败原因`  as a   " +
                    "join  `lsh保险战败原因附属` as b  on b.`保险战败原因编号`=a.`新保战败原因编号`\n" +
                    "WHERE b.`战败分类`=:reason GROUP BY a.`新保战败原因编号` ");
        Map params=Maps.newHashMap();
        params.put("reason",reason);
        return  this.getList(sql.toString(),params);

    }

    @Override
   public List getcarForceInsInformation(String InsId){
        try{
            StringBuffer sql=new StringBuffer("" +
                    "  SELECT   " +
                    "    b.`被保人姓名`,    " +
                    "    ( CASE   WHEN  b.`被保人称谓`='先生' THEN '男'  " +
                    "      WHEN b.`被保人称谓`='女士' THEN '女'  END ) AS '性别',  " +
                    "    b.`被保人称谓`,  " +
                    "    b.`客户类型`,  " +
                    "    IF(b.`客户类型`<>'公司',b.`身份证`,null) AS 被保人身份证号 ,  " +
                    "    b.`联系电话`,  " +
                    "    IF(b.`客户类型`='公司',b.`身份证`,null) AS 组织机构证代码 ,  " +
                    "    b.`省份` ,  " +
                    "    b.`城市`,  " +
                    "    b.`行政区`,  " +
                    "    b.`邮编`,  " +
                    "    b.`详细地址`,  " +
                    "    b.`交强险出单日期`,  " +
                    "    a.`交强险投保期`,  " +
                    "    a.`交强险保险公司`, " +
                    "  b.`交强险出单日期`   as 出单日期 , " +
                    "    c.`折后价` ,  " +
                    "    if( m.险种 = '车船税' AND m.第N年 = 1,m.折后价,0) 车船税," +
                    "    a.`交强险保单号`,  " +
                    "    a.`交强险开始日期`,  " +
                    "    a.`交强险结束日期`,  " +
                    "    d.`批单号`,  " +
                    "    d.`批单日期`," +
                    "     d.批单类型  ," +
                    "    d.`退保日期`,  " +
                    "   d.退保备注," +
                    "    d.`保单终止日期`," +
                    "    d.endorsementMoney " +
                    "    from `报价保险表`  AS a  " +
                    "LEFT JOIN `报价单表` AS e ON e.`保险编号`=a.`保险编号` " +
                    " LEFT JOIN `lsh保险申请附属` AS b ON e.`初始报价单号`=b.`初始报价单号` " +
                    "  LEFT JOIN `报价保险详细表` AS c ON ( a.`保险编号` = c.`保险编号` AND c.`险种` = '交强险' AND c.`第N年` = 1) " +
                    "   LEFT JOIN `报价保险详细表` AS m ON (a.`保险编号` = m.`保险编号` AND m.`险种` = '车船税' AND m.`第N年` = 1) " +
                    "  LEFT JOIN (select MAX(a.id) max_xx_id ,a.初始报价单号 from   lsh保险申请表附属_详细  as a  where a.`类型`=0 GROUP BY   初始报价单号, 第N年     )  max_xx_id  ON max_xx_id.`初始报价单号`=e.`初始报价单号`" +
                    " LEFT JOIN `lsh保险申请表附属_详细` AS d ON ( d.`id`= max_xx_id.max_xx_id    AND d.`第N年` = 1) " +
                    " WHERE a.`保险编号`=:InsId  ORDER BY d.`更新时间` DESC limit 1 ");
            Map  params=Maps.newHashMap();
            params.put("InsId",InsId);
            return this.getList(sql.toString(),params);
        }
        catch(Exception e){
            logger.error("运行异常。", e);
        }
        return null;
    }


    @Override
    public List getcarCommercialInsInformation(String InsId){
        try{
            StringBuffer sql=new StringBuffer(" " +
                    "    SELECT   " +
                    "    b.`被保人姓名`,    " +
                    "    ( CASE   WHEN  b.`被保人称谓`='先生' THEN '男'  " +
                    "      WHEN b.`被保人称谓`='女士' THEN '女'  END ) AS '性别',  " +
                    "    b.`被保人称谓`,  " +
                    "    b.`客户类型`,  " +
                    "    IF(b.`客户类型`<>'公司',b.`身份证`,null) AS 被保人身份证号 ,  " +
                    "    b.`联系电话`,  " +
                    "    IF(b.`客户类型`='公司',b.`身份证`,null) AS 组织机构证代码 ,  " +
                    "    b.`省份` ,  " +
                    "    b.`城市`,  " +
                    "    b.`行政区`,  " +
                    "    b.`邮编`,  " +
                    "    b.`详细地址`,  " +
                    "    b.`商业险出单日期`, " +
                    "    a.`商业险投保期`, " +
                    "    a.`商业险保险公司`, " +
                    "     b.`商业险出单日期`   as 出单日期   ,   " +
//                    "    SUM( IFNULL(c.`折后价`,0)) +SUM( IFNULL(c.`不计免赔`,0)) 折后价 ,   " +
                    "    ( SELECT SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税'   and bjbx.不计免赔 is not NULL ,bjbx.不计免赔,0)) \n" +
                    "    +SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税'   and bjbx.折后价 is not NULL ,bjbx.折后价,0))\n" +
                    "    FROM  报价保险详细表 bjbx  WHERE     c.保险编号 = bjbx.保险编号  ) 折后价  , " +
                    "    a.`商业险保单号`, " +
                    "    a.`商业险开始日期`,  " +
                    "    a.`商业险结束日期`,  " +
                    "    d.`批单号`,  " +
                    "    d.`批单日期`,  " +
                    "    d.`退保日期`,  " +
                    "    d.`保单终止日期` ," +
                    "     d.批单类型, " +
                    "    ifnull(c.`第N年`,1) 第N年   ," +
                    "  d.退保备注 ," +
                    "   d.endorsementMoney " +
                    "    from `报价保险表`  AS a  " +
                    " LEFT   JOIN `报价单表` AS e ON e.`保险编号`=a.`保险编号`  " +
                    " LEFT  JOIN `lsh保险申请附属` AS b ON  e.`初始报价单号`=b.`初始报价单号` " +
                    " LEFT  JOIN `报价保险详细表` AS c ON ( a.`保险编号` = c.`保险编号` AND c.`险种` <> '交强险' AND c.`险种` <> '车船税') " +
                    "   LEFT JOIN (" +
                    " SELECT * FROM ( SELECT MAX(a.id) AS ida FROM `lsh保险申请表附属_详细` AS a GROUP BY a.初始报价单号, a.第N年, a.类型 ) AS a JOIN `lsh保险申请表附属_详细` AS b ON a.ida = b.id" +
                    ") AS d ON ( e.`初始报价单号` = d.`初始报价单号` AND d.`类型` = 1 AND d.`第N年` = c.`第N年`) " +
                    "    WHERE a.`保险编号`=:InsId " +
                    "    GROUP BY  c.`第N年`   " );
            Map  params=Maps.newHashMap();
            params.put("InsId",InsId);
            return this.getList(sql.toString(),params);
        }
        catch(Exception e){
            logger.error("运行异常。", e);
        }
        return null;
    }
    @Override

    public List getTradeInsInfoList(InsuranceService.TradeInsInfo tradeInsInfo){
        try {
            StringBuffer sql=new StringBuffer(" " +
                    "  SELECT  a.`险种`," +
                    "  a.`参数` as  保额," +
                    "  a.`折后价` as 保费 ," +
                    "  a.`不计免赔`  " +
                    "   FROM `报价保险详细表` AS a " +
                    "    WHERE  a.`险种`<>'交强险'  AND " +
                    "    a.`保险编号`=:insId and a.`第N年`=:year ");
            Map params=Maps.newHashMap();
            params.put("insId",tradeInsInfo.insId);
            params.put("year",tradeInsInfo.year);
            return  this.getList(sql.toString(),params);

        } catch (Exception e) {
            logger.error("运行异常。", e);
            return null;
        }
    }

    @Override
    public List getInsIcWorkInfo(String permit, String userName) {
        try {
            Map params = Maps.newHashMap();
            params.put("permit", permit);
            params.put("userName", userName);
            params.put("likePermit", permit+"%");
            StringBuffer sql = new StringBuffer( //当日保险待报价总数
                    "  SELECT\n" +
                            " COUNT(1) 待报价总数\n" +
                            " FROM\n" +
                            " `报价单表` AS c\n" +
                            " JOIN  vw_max_quotedcode_all AS x ON x.`报价编号`=c.`报价编号`\n" +
                            " JOIN 报价保险表 AS a ON a.`保险编号` = c.`保险编号`\n" +
                            " JOIN insurance_log AS b ON c.`初始报价单号` = b.orgQsn\n" +
                            " JOIN ( SELECT *, MAX(ab.id) AS `保险单` FROM insurance_log AS ab   where  ab.orgQsn like  :likePermit   GROUP BY ab.orgQsn ) AS y ON b.orgQsn = y.orgQsn\n" +
                            " LEFT JOIN `lsh库存表` AS d ON d.`库存编号` = c.`库存编号`\n" +
                            " JOIN `顾客记录汇总` AS e ON e.`顾客编号` = c.`顾客编号`\n" +
                            " JOIN `用户` AS f ON c.`销售顾问` = f.`用户名`\n" +
                            " JOIN `lsh保险申请附属` AS g ON g.`初始报价单号` = c.`初始报价单号`\n" +
                            " JOIN `lsh车型` AS h ON c.`车型编号` = h.`车型编号`\n" +
                            " LEFT JOIN 报价订单表 l ON l.订单编号 = c.订单编号\n" +
                            " WHERE\n" +
                            " b.id = y.`保险单`\n" +
                            " AND b.state = '01'\n" +
                            " AND c.许可 =:permit\n" +
                            " AND (g.`操作人` =:userName or g.`操作人`is null or g.`操作人`='')\n");
            StringBuffer sql2 = new StringBuffer(" " +
                    " SELECT\n" +
                    " COUNT(1) 已完成\n" +
                    " FROM\n" +
                    " `报价单表` AS c\n" +
                    " JOIN  vw_max_quotedcode_all AS x ON x.`报价编号`=c.`报价编号`\n" +
                    " JOIN 报价保险表 AS a ON a.`保险编号` = c.`保险编号`\n" +
                    " JOIN insurance_log AS b ON c.`初始报价单号` = b.orgQsn\n" +
                    " JOIN ( SELECT *, MAX(ab.id) AS `保险单` FROM insurance_log AS ab    where  ab.orgQsn like  :likePermit   GROUP BY ab.orgQsn ) AS y ON b.orgQsn = y.orgQsn\n" +
                    " LEFT JOIN `lsh库存表` AS d ON d.`库存编号` = c.`库存编号`\n" +
                    " JOIN `顾客记录汇总` AS e ON e.`顾客编号` = c.`顾客编号`\n" +
                    " JOIN `用户` AS f ON c.`销售顾问` = f.`用户名`\n" +
                    " JOIN `lsh保险申请附属` AS g ON g.`初始报价单号` = c.`初始报价单号`\n" +
                    " JOIN `lsh车型` AS h ON c.`车型编号` = h.`车型编号`\n" +
                    " LEFT JOIN 报价订单表 l ON l.订单编号 = c.订单编号\n" +
                    " WHERE\n" +
                    " b.id = y.`保险单`\n" +
                    " AND b.state = '02'\n" +
                    " AND c.许可 =:permit\n" +
                    " AND g.`操作人` =:userName\n" +
                    " AND  DATE_FORMAT(g.更新时间,'%Y-%m-%d')=CURDATE()\n"); //当日保险待报价总数
            List result = this.getList(sql.toString(), params);
            Map data = (Map) result.get(0);
            data.putAll((Map) this.getList(sql2.toString(), params).get(0));
            return result;
        }catch (Exception e){
            logger.error("保险报价查询失败", e);
            return  null;
        }
    }

    public List getInsCarInfo(String code) {
        try {

            StringBuffer sql = new StringBuffer("" +
                    "SELECT b.`品牌`, \n" +
                    "if(b.`来源`='PbP', CONCAT('PbP ',b.`级别`), b.`级别` ) as  级别, \n" +
                    "b.`车型`,  \n" +
                    "b.`车型详细`,c.`车架号`,c.`发动机号`,c.核载人数 AS 核定载客人数,\n" +
                    "c.`机动车种类`,c.核载质量,c.排量,c.功率,d.`牌照号` AS 车牌号,\n" +
                    "e.`使用性质`,e.`登记日期`,a.`交车`,c.库存编号,d.`退票时间`\n" +
                    "FROM `报价单表` AS a \n" +
                    "JOIN  vw_max_quotedcode_all AS x ON x.`报价编号`=a.`报价编号`\n" +
                    "JOIN   lsh保险申请附属 as e on a.`初始报价单号`=e.`初始报价单号`\n" +
                    "JOIN `lsh车型`  AS b ON a.`车型编号`=b.`车型编号` \n" +
                    "LEFT JOIN  `lsh库存表`  AS c ON  a.`库存编号`=c.`库存编号` \n" +
                    "LEFT JOIN  `报价交车表` AS d ON a.`交车`=d.编号\n" +
                    "WHERE \n" +

                    "a.`初始报价单号`=:code\n");
            Map params = Maps.newHashMap();
            params.put("code", code);
            return this.getList(sql.toString(), params);
        } catch (Exception e) {
            logger.error("保险车辆查询信息失败", e);
            return null;
        }

    }

    @Override
    public List getInsHistoryInfo(String code) {
        Map params = Maps.newHashMap();
        params.put("code", code);
        StringBuffer sql = new StringBuffer(" SELECT a.* ,c.`退票提交时间` from `lsh保险申请表附属_详细`   as a\n" +
                "left JOIN  ( SELECT  *,MAX(aa.`报价编号`) AS `单号`  FROM  `报价单表` AS aa  group by `初始报价单号` )AS x ON x.`初始报价单号`=a.`初始报价单号`\n" +
                "left JOIN `报价单表` AS b ON x.`单号`=b.`报价编号`\n" +
                "left JOIN `报价交车表` as c on c.编号 = b.交车\n" +
                "where a.`初始报价单号`=:code ");
        return this.getList(sql.toString(), params);
    }



    public List findQuanClassAndidMaxDate(String ids) {


        StringBuffer sql = new StringBuffer(" select   \n" +
                " max(a.id) id from   \n" +
                "`lsh保险申请表附属_详细` a    \n" +
                "where a.`初始报价单号`=:insId   \n" +
                "and a.`类型`='1'     \n" +
                "GROUP BY a.`第N年`      ");
        Map params = Maps.newHashMap();
        params.put("insId", ids);
        return this.getList(sql.toString(), params);
    }

    //获取交强险和商业险总价
    public List getInsPrice(Integer areaId,String permit, String inscode, String brand) {
        Map params = Maps.newHashMap();
        params.put("inscode", inscode);
        params.put("permit", permit);
        params.put("areaId",areaId);
        params.put("brand",brand);
        String sql = " SELECT SUM( IF ( `险种` = '交强险', `折后价`, 0 )) + SUM( IF ( `险种` = '交强险', `不计免赔`, 0 )) AS 交强险, \n" +
                "SUM( IF ( `险种` <> '交强险'AND `险种`<>'车船税', `折后价`, 0 )) + SUM( IF ( `险种` <> '交强险' AND `险种`<>'车船税', `不计免赔`, 0 )) AS 商业险,\n" +
                "c.`保险公司编号` as 交强险公司编号,d.`保险公司编号` as 商业险公司编号\n" +
                "FROM `报价保险表` AS b\n" +
                "LEFT   JOIN   `报价保险详细表` AS a ON a.`保险编号`=b.`保险编号`\n" +
                "LEFT JOIN  LSH保险公司设置 AS c ON c.`保险公司`=b.`交强险保险公司` and (c.area_id = :areaId or  ISNULL(c.area_id) ) and c.brand = :brand\n" +
                "LEFT JOIN  LSH保险公司设置 AS d ON d.`保险公司`=b.商业险保险公司 and (d.area_id =:areaId or ISNULL(d.area_id) ) and d.brand = :brand\n" +
                "JOIN  LSH保险公司使用 AS e ON e.`保险公司编号`=c.保险公司编号 and e.许可=:permit\n" +
                "JOIN  LSH保险公司使用 AS f ON f.`保险公司编号`=d.保险公司编号 and f.许可=:permit\n" +
                "where b.`保险编号`=:inscode";

        if(logger.isDebugEnabled()){
            logger.debug("params={}", JsonObjectUtils.writeAsJson(params));
        }
        return this.getList(sql.toString(), params);
    }

    //获取对应商业险 交强险手续费额度
    public List getInsFactorage(String insA, String insB, String permit, String state) {
        Map params = Maps.newHashMap();
        params.put("insA", insA);
        params.put("insB", insB);
        params.put("permit", permit);
        params.put("state", state);
        String sql = "SELECT  " +
                "  MAX( CASE WHEN `保险公司编号` =:insA THEN `交强险手续费` END ) AS 交强险手续费, \n" +
                "  MAX( CASE WHEN `保险公司编号` =:insB THEN `商业险手续费` END ) AS 商业险手续费\n" +
                "from LSH保险手续费  where `许可`=:permit  AND 客户类别=:state ";
        return this.getList(sql.toString(), params);

    }

    public List getconfirmInsState(String userName, String quCode, String logTime) {
        Map params = Maps.newHashMap();
        params.put("userName", userName);
        params.put("quCode", quCode);
        params.put("logTime", logTime);
        String sql = "select 1 from insurance_log as a where a.orgQsn =:quCode and logTime>:logTime and a.logUser<>:userName  ";

        return this.getList(sql.toString(), params);

    }

    public List getInsCost(String insCode) {
        Map params = Maps.newHashMap();
        params.put("insCode", insCode);
        String sql = " select    SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税' AND bjbx.第N年 = 1 and bjbx.不计免赔 is not NULL ,bjbx.不计免赔,0)) +\n" +
                "SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税' AND bjbx.第N年 = 1 and bjbx.折后价 is not NULL ,bjbx.折后价,0))  首年商业险,\n" +
                "SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税'   and bjbx.不计免赔 is not NULL ,bjbx.不计免赔,0)) +\n" +
                "SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税'   and bjbx.折后价 is not NULL ,bjbx.折后价,0))  一保多年商业险保费,\n" +
                "if( bjbx.险种 = '交强险' AND bjbx.第N年 = 1,bjbx.折后价,0)  交强险\n" +
                "from   `报价保险详细表` as bjbx     where bjbx.`保险编号`=:insCode  GROUP BY bjbx.`保险编号`\n";
        return this.getList(sql.toString(), params);

    }

    public List getPolicyReasonInfo(String permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        String sql = " select * from  insurance_cancel_reason as a where  a.permit=:permit  ORDER BY a.update_time DESC ";
        return this.getList(sql.toString(), params);
    }

    @Override
    public PageData getOtherInsuranceInfo(String permit, InsuranceService.OtherInsuranceInfo info) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);

        String sql = " select \n" +
                "用户.`人员姓名`,\n" +
                "carinfo.sales," +
                "carinfo.carid,\n" +
                "carinfo.insName,\n" +
                "carinfo.insTel,\n" +
                "carinfo.carnumber,\n" +
                "carinfo.brand,\n" +
                "carinfo.model,\n" +
                "carinfo.modelshort,\n" +
                "if(insbuy.forced_op_time is not null AND insbuy.forced_op_time <> '','已出单','未出单') 交强险出单状态,\n" +
                "insbuy.forced_number,\n" +
                "insbuy.forced_ins_com,\n" +
                "  if(buyinfo.insname='交强险',discount_price,0 ) 交强险保费,\n" +
                "  if(buyinfo.insname='车船税',discount_price,0 ) 车船税,\n" +
                "if(insbuy. bus_op_time is not null and insbuy. bus_op_time <> '','已出单','未出单') 商业险出单状态,\n" +
                "insbuy.bus_number,\n" +
                "insbuy.bus_ins_com,\n" +
                "carinfo.VIN,\n" +
                " SUM( if(eninfo.`year`=1 and  buyinfo.insname <>'交强险' and buyinfo.insname<>'车船税' AND  buyinfo.discount_price IS NOT NULL ,buyinfo.discount_price, 0)) +\n" +
                "  SUM( if(eninfo.`year`=1 and  buyinfo.insname <>'交强险' and buyinfo.insname<>'车船税'  AND  buyinfo.SDEW IS NOT NULL ,buyinfo.SDEW, 0)) 首年商业险\n" +
                " FROM\n" +
                "t_insurance_car_info carinfo\n" +
                "left join t_ins_buy  insbuy ON carinfo.carid = insbuy.carid\n" +
                "left  JOIN t_endorsement_info eninfo on  insbuy.insID=eninfo.insID and eninfo.state='0'\n" +
                "left JOIN t_ins_buyinfo buyinfo on eninfo.pdID=buyinfo.pdID\n" +
                "left JOIN 用户 `用户` on 用户.用户名=carinfo.sales\n" +
                "where  carinfo.permit=:permit \n";

        /*
        icPolicyNo(交强险保单号)(模糊查询)
                bcPolicyNo(商业险保单号)(模糊查询)
                icInsuranceCpy(交强险保险公司)(精确查询)
                bcInsuranceCpy(商业险保险公司)(精确查询)

                sale(销售顾问)(精确查询)

                cusName(客户姓名)(模糊查询)
                carBrand(品牌)(模糊查询)
                carModel(车型)(模糊查询)*/

        if (!Strings.isNullOrEmpty(info.icPolicyNo)) {//交强险保单号
            sql += "and  insbuy.forced_number like :icPolicyNo\n";
            params.put("icPolicyNo", "%" + info.icPolicyNo + "%");
        }
        if (!Strings.isNullOrEmpty(info.icInsuranceCpy)) {//交强险保险公司
            sql += "and insbuy.forced_ins_com = :icInsuranceCpy \n";
            params.put("icInsuranceCpy", info.icInsuranceCpy);
        }
        if (!Strings.isNullOrEmpty(info.bcPolicyNo)) {//商业险保单号
            sql += "and insbuy.bus_number like :bcPolicyNo\n";
            params.put("bcPolicyNo", "%" + info.bcPolicyNo + "%");
        }
        if (!Strings.isNullOrEmpty(info.bcInsuranceCpy)) {//商业险公司
            sql += "and insbuy.bus_ins_com =:bcInsuranceCpy\n";
            params.put("bcInsuranceCpy", info.bcInsuranceCpy);
        }
        if (!Strings.isNullOrEmpty(info.sale)) {//销售顾问名
            sql += "and 用户.`人员姓名` =:sale\n";
            params.put("sale", info.sale);
        }
        if (!Strings.isNullOrEmpty(info.cusName)) {
            sql += "and carinfo.insName like :cusName\n";
            params.put("cusName", "%" + info.cusName + "%");
        }
        /*if (!Strings.isNullOrEmpty(tciNo)) {
            sql +="and carinfo.insTel \n" +;
            params.put("tciNo", "%" + tciNo + "%");
        }*/
        if (!Strings.isNullOrEmpty(info.carBrand)) {
            sql += "and carinfo.brand like :carBrand \n";
            params.put("carBrand", "%" + info.carBrand + "%");
        }
        if (!Strings.isNullOrEmpty(info.carModel)) {
            sql += "and carinfo.model  like :carModel";
            params.put("carModel", "%" + info.carModel + "%");
        }
        sql += " GROUP BY  carinfo.carid";
        return this.getPage(sql, params, info.getPageSize(), info.getPageIndex());
    }


    @Override
    public PageData getOthergetInsuranceAccountInfo(String permit, InsuranceService.OtherInsuranceInfo info) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        String sql = "select \n" +
                "用户.`人员姓名`,\n" +
                "carinfo.sales, \n" +
                "carinfo.carid,\n" +
                "carinfo.insName,\n" +
                "carinfo.insTel,\n" +
                "carinfo.carnumber,\n" +
                "carinfo.brand,\n" +
                "carinfo.model,\n" +
                "carinfo.modelshort,\n" +
                "if(insbuy.forced_op_time is not null,'已出单','未出单') 交强险出单状态,\n" +
                "insbuy.forced_number,\n" +
                "insbuy.forced_ins_com,\n" +
                "if(buyinfo.insname='交强险',discount_price,0 ) 交强险保费,\n" +
                "if(buyinfo.insname='车船税',discount_price,0 ) 车船税,\n" +
                "if(insbuy. bus_op_time is not null,'已出单','未出单') 商业险出单状态,\n" +
                "insbuy.bus_number,\n" +
                "insbuy.bus_ins_com,\n" +
                "eninfo.`year`,\n" +
                "carinfo.VIN,\n" +
                "SUM( if(      buyinfo.insname <>'交强险' and buyinfo.insname<>'车船税' AND  buyinfo.discount_price IS NOT NULL ,buyinfo.discount_price, 0)) + \n" +
                "SUM( if(    buyinfo.insname <>'交强险' and buyinfo.insname<>'车船税'  AND  buyinfo.SDEW IS NOT NULL ,buyinfo.SDEW, 0))  商业险保费,\n" +
              
                
                 /* --   1.车辆损失险/保额    车辆损失险/保费    车辆损失险/不计免赔*/ 
                "SUM(  IF(buyinfo.insname = '车辆损失险', buyinfo.parameter, 0) ) AS `车辆损失险/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '车辆损失险', buyinfo.discount_price, 0) ) AS  `车辆损失险/保费`,\n" +
                "SUM(  IF(buyinfo.insname = '车辆损失险', buyinfo.SDEW, 0) ) AS  `车辆损失险/不计免赔`,\n" +
            
                /*  --2. 商业三者责任险/保额  商业三者责任险/保费  商业三者责任险/不计免赔*/
                "SUM(  IF(buyinfo.insname = '商业三者责任险', buyinfo.parameter, 0) )  AS `商业三者责任险/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '商业三者责任险', buyinfo.discount_price, 0) )  AS `商业三者责任险/保费`,\n" +
                "SUM(  IF(buyinfo.insname = '商业三者责任险', buyinfo.SDEW, 0) ) AS `商业三者责任险/不计免赔`,\n" +
                
                /* -- 3.全车盗抢险/保费  全车盗抢险/不计免赔 */
                "SUM(  IF(buyinfo.insname = '全车盗抢险', buyinfo.discount_price, 0) )  AS `全车盗抢险/保费`,\n" +
                "SUM(  IF(buyinfo.insname = '全车盗抢险', buyinfo.SDEW, 0) )  AS `全车盗抢险/不计免赔`,\n" +
                
                /* -- 4. 车上人员责任险-司机/保额  车上人员责任险-司机/保费  车上人员责任险-司机/不计免赔*/
                "SUM(  IF(buyinfo.insname = '车上人员责任险 - 司机', buyinfo.parameter, 0) )  AS  `车上人员责任险-司机/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '车上人员责任险 - 司机', buyinfo.discount_price, 0) ) AS  `车上人员责任险-司机/保费`,\n" +
                "SUM(  IF(buyinfo.insname = '车上人员责任险 - 司机', buyinfo.SDEW, 0) ) AS `车上人员责任险-司机/不计免赔`,\n" +
                
                /*  -- 5. 车上人员责任险-乘客/保额  车上人员责任险-乘客/保费  车上人员责任险-乘客/不计免赔*/
                "SUM(  IF(buyinfo.insname = '车上人员责任险 - 乘客', buyinfo.parameter, 0) ) AS  `车上人员责任险-乘客/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '车上人员责任险 - 乘客', buyinfo.discount_price, 0) ) AS `车上人员责任险-乘客/保费`,\n" +
                "SUM(  IF(buyinfo.insname = '车上人员责任险 - 乘客', buyinfo.SDEW, 0) ) AS  `车上人员责任险-乘客/不计免赔`,\n" +
                
                /*  -- 6.玻璃单独破碎险/保额  玻璃单独破碎险/保费 */
                "SUM(  IF(buyinfo.insname = '玻璃单独破碎险', buyinfo.discount_price, 0) ) AS `玻璃单独破碎险/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '玻璃单独破碎险', buyinfo.SDEW, 0) ) AS `玻璃单独破碎险/保费`,\n" +
                
                /*  -- 7.车身划痕损失险/保额  车身划痕损失险/保费  车身划痕损失险/不计免赔*/
                "SUM(  IF(buyinfo.insname = '车身划痕损失险', buyinfo.parameter, 0) )  AS `车身划痕损失险/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '车身划痕损失险', buyinfo.discount_price, 0) )  AS `车身划痕损失险/保费`,\n" +
                "SUM(  IF(buyinfo.insname = '车身划痕损失险', buyinfo.SDEW, 0) ) AS  `车身划痕损失险/不计免赔`,\n" +
                
                /*  -- 8.自燃损失险/保费 */
                "SUM(  IF(buyinfo.insname = '自燃损失险', buyinfo.discount_price, 0) ) AS  `自燃损失险/保费` ,\n" +
                
                /*  -- 9.发动机涉水损失险/保费 */
                "SUM(  IF(buyinfo.insname = '发动机涉水损失险', buyinfo.discount_price, 0) ) AS `发动机涉水损失险/保费` ,\n" +
                
                /*  --  10. 倒车镜、车灯单独损失险/保费 */
                "SUM(  IF(buyinfo.insname = '倒车镜、车灯单独损失险', buyinfo.discount_price, 0) ) AS `倒车镜、车灯单独损失险/保费` ,\n" +
                
                /* --  11.新增设备损失险/保额  新增设备损失险/保费*/
                "SUM(  IF(buyinfo.insname = '新增设备损失险', buyinfo.parameter, 0) ) AS `新增设备损失险/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '新增设备损失险', buyinfo.discount_price, 0) ) AS `新增设备损失险/保费`,\n" +
                
                /*  --  12.指定修理厂险/保费 */
                "SUM(  IF(buyinfo.insname = '指定修理厂险', buyinfo.parameter, 0) ) AS `指定修理厂险/保费`,\n" +
                
                /*  --  13.修理期间费用补偿险/保额     修理期间费用补偿险/保费*/
                "SUM(  IF(buyinfo.insname = '修理期间费用补偿险', buyinfo.parameter, 0) ) AS `修理期间费用补偿险/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '修理期间费用补偿险', buyinfo.discount_price, 0) ) AS `修理期间费用补偿险/保费` ,\n" +
                
                /*  --  14.车上货物责任险/保额  车上货物责任险/保费*/
                "SUM(  IF(buyinfo.insname = '车上货物责任险', buyinfo.parameter, 0) ) AS `车上货物责任险/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '车上货物责任险', buyinfo.discount_price, 0) )  AS `车上货物责任险/保费` ,\n" +
                
                /*  -- 15.精神损害抚慰金责任险/保额  精神损害抚慰金责任险/保费*/
                "SUM(  IF(buyinfo.insname = '精神损害抚慰金责任险', buyinfo.parameter, 0) ) AS `精神损害抚慰金责任险/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '精神损害抚慰金责任险', buyinfo.discount_price, 0) ) AS `精神损害抚慰金责任险/保费` ,\n" +
                
                /* -- 16.机动车损失保险无法找到第三方特约险/保费*/
                "SUM(  IF(buyinfo.insname = '机动车损失保险无法找到第三方特约险', buyinfo.discount_price, 0) ) AS `机动车损失保险无法找到第三方特约险/保费` ,\n" +
                
                /*  -- 17. 自定义险种一/保额  自定义险种一/保费\\t*/
                "SUM(  IF(buyinfo.insname = '自定义险种一', buyinfo.parameter, 0) ) AS `自定义险种一/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '自定义险种一', buyinfo.discount_price, 0) ) AS `自定义险种一/保费` ,\n" +
                
                /*  -- 18.自定义险种二/保额  自定义险种二/保费  附加险/不计免赔*/
                "SUM(  IF(buyinfo.insname = '自定义险种二', buyinfo.parameter, 0) ) AS  `自定义险种二/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '自定义险种二', buyinfo.discount_price, 0) ) AS `自定义险种二/保费`,\n" +
                "SUM(  IF(buyinfo.insname = '附加险', buyinfo.SDEW, 0) ) AS `附加险/不计免赔`\n" +
                "FROM\n" +
                "t_insurance_car_info carinfo\n" +
                "left join t_ins_buy  insbuy ON carinfo.carid = insbuy.carid\n" +
                "left  JOIN t_endorsement_info eninfo on  insbuy.insID=eninfo.insID and eninfo.state='0'\n" +
                "left JOIN t_ins_buyinfo buyinfo on eninfo.pdID=buyinfo.pdID\n" +
                "left JOIN 用户 `用户` on 用户.用户名=carinfo.sales    \n" +
                "where  carinfo.permit=:permit \n";


        if (!Strings.isNullOrEmpty(info.icPolicyNo)) {//交强险保单号
            sql += "and  insbuy.forced_number like :icPolicyNo\n";
            params.put("icPolicyNo", "%" + info.icPolicyNo + "%");
        }
        if (!Strings.isNullOrEmpty(info.icInsuranceCpy)) {//交强险保险公司
            sql += "and insbuy.forced_ins_com = :icInsuranceCpy \n";
            params.put("icInsuranceCpy", info.icInsuranceCpy);
        }
        if (!Strings.isNullOrEmpty(info.bcPolicyNo)) {//商业险保单号
            sql += "and insbuy.bus_number like :bcPolicyNo\n";
            params.put("bcPolicyNo", "%" + info.bcPolicyNo + "%");
        }
        if (!Strings.isNullOrEmpty(info.bcInsuranceCpy)) {//商业险公司
            sql += "and insbuy.bus_ins_com =:bcInsuranceCpy\n";
            params.put("bcInsuranceCpy", info.bcInsuranceCpy);
        }
        if (!Strings.isNullOrEmpty(info.sale)) {//销售顾问名
            sql += "and 用户.`人员姓名` =:sale\n";
            params.put("sale", info.sale);
        }
        if (!Strings.isNullOrEmpty(info.cusName)) {
            sql += "and carinfo.insName like :cusName\n";
            params.put("cusName", "%" + info.cusName + "%");
        }
        /*if (!Strings.isNullOrEmpty(tciNo)) {
            sql +="and carinfo.insTel \n" +;
            params.put("tciNo", "%" + tciNo + "%");
        }*/
        if (!Strings.isNullOrEmpty(info.carBrand)) {
            sql += "and carinfo.brand like :carBrand \n";
            params.put("carBrand", "%" + info.carBrand + "%");
        }
        if (!Strings.isNullOrEmpty(info.carModel)) {
            sql += "and carinfo.model  like :carModel";
            params.put("carModel", "%" + info.carModel + "%");
        }
        sql += " GROUP BY  carinfo.carid,eninfo.`year`";
        return this.getPage(sql, params, info.getPageSize(), info.getPageIndex());

    }

    @Override
    public PageData exportOtherInsuranceAccountInfo(String permit, String icPolicyNo, String bcPolicyNo, String icInsuranceCpy, String bcInsuranceCpy,
                                                    String sale, String cusName, String carBrand, String carModel, int pageSize, int pageIndex) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        String sql = "select \n" +
                "carinfo.carid,\n" +
                "carinfo.carnumber,\n" +
                "eninfo.`year`,\n" +
                "carinfo.insName,\n" +
                "carinfo.insTel,\n" +
                "用户.`人员姓名`,\n" +
                "carinfo.sales, \n" +
                "carinfo.brand,\n" +
                "carinfo.model,\n" +
                "carinfo.modelshort,\n" +
                "carinfo.VIN,\n" +
                "date_format(carinfo.checkdate,'%Y-%m-%d') checkdate,\n" +

                "insbuy.forced_number,\n" +
                "insbuy.forced_ins_com,\n" +
                "if(buyinfo.insname='交强险',discount_price,0 ) 交强险保费,\n" +
                "date_format(insbuy.forced_op_time,'%Y-%m-%d') forced_op_time,\n" +
                "insbuy.forced_poundage,\n" +
                "date_format(insbuy.forced_insure_start,'%Y-%m-%d') forced_insure_start,\n" +
                "date_format(insbuy.forced_insure_end,'%Y-%m-%d') forced_insure_end,\n" +
                "if(insbuy.forced_op_time is not null,'已出单','未出单') 交强险出单状态,\n" +

                "if(buyinfo.insname='车船税',discount_price,0 ) 车船税,\n" +

                "insbuy.bus_number,\n" +
                "insbuy.bus_ins_com,\n" +
                "SUM( if(      buyinfo.insname <>'交强险' and buyinfo.insname<>'车船税' AND  buyinfo.discount_price IS NOT NULL ,buyinfo.discount_price, 0)) + \n" +
                "SUM( if(    buyinfo.insname <>'交强险' and buyinfo.insname<>'车船税'  AND  buyinfo.SDEW IS NOT NULL ,buyinfo.SDEW, 0))  商业险保费,\n" +
                "date_format(insbuy.bus_op_time,'%Y-%m-%d') bus_op_time,\n" +
                "insbuy.bus_poundage,\n" +
                "date_format(insbuy.bus_insure_start,'%Y-%m-%d') bus_insure_start,\n" +
                "date_format(insbuy.bus_insure_end,'%Y-%m-%d') bus_insure_end,\n" +
                "if(insbuy. bus_op_time is not null,'已出单','未出单') 商业险出单状态,\n" +

                "case when eninfo.endorsement_type = '00' then '批增' \n" +
                "when eninfo.endorsement_type = '01' then '批减' \n" +
                "when eninfo.endorsement_type = '02' then '批退' \n" +
                "else '' end endorsement_type,\n" +


                 /* --   1.车辆损失险/保额    车辆损失险/保费    车辆损失险/不计免赔*/
                "SUM(  IF(buyinfo.insname = '车辆损失险', buyinfo.parameter, 0) ) AS `车辆损失险/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '车辆损失险', buyinfo.discount_price, 0) ) AS  `车辆损失险/保费`,\n" +
                "SUM(  IF(buyinfo.insname = '车辆损失险', buyinfo.SDEW, 0) ) AS  `车辆损失险/不计免赔`,\n" +

                /*  --2. 商业三者责任险/保额  商业三者责任险/保费  商业三者责任险/不计免赔*/
                "SUM(  IF(buyinfo.insname = '商业三者责任险', buyinfo.parameter, 0) )  AS `商业三者责任险/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '商业三者责任险', buyinfo.discount_price, 0) )  AS `商业三者责任险/保费`,\n" +
                "SUM(  IF(buyinfo.insname = '商业三者责任险', buyinfo.SDEW, 0) ) AS `商业三者责任险/不计免赔`,\n" +

                /* -- 3.全车盗抢险/保费  全车盗抢险/不计免赔 */
                "SUM(  IF(buyinfo.insname = '全车盗抢险', buyinfo.discount_price, 0) )  AS `全车盗抢险/保费`,\n" +
                "SUM(  IF(buyinfo.insname = '全车盗抢险', buyinfo.SDEW, 0) )  AS `全车盗抢险/不计免赔`,\n" +

                /* -- 4. 车上人员责任险-司机/保额  车上人员责任险-司机/保费  车上人员责任险-司机/不计免赔*/
                "SUM(  IF(buyinfo.insname = '车上人员责任险 - 司机', buyinfo.parameter, 0) )  AS  `车上人员责任险-司机/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '车上人员责任险 - 司机', buyinfo.discount_price, 0) ) AS  `车上人员责任险-司机/保费`,\n" +
                "SUM(  IF(buyinfo.insname = '车上人员责任险 - 司机', buyinfo.SDEW, 0) ) AS `车上人员责任险-司机/不计免赔`,\n" +

                /*  -- 5. 车上人员责任险-乘客/保额  车上人员责任险-乘客/保费  车上人员责任险-乘客/不计免赔*/
                "SUM(  IF(buyinfo.insname = '车上人员责任险 - 乘客', buyinfo.parameter, 0) ) AS  `车上人员责任险-乘客/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '车上人员责任险 - 乘客', buyinfo.discount_price, 0) ) AS `车上人员责任险-乘客/保费`,\n" +
                "SUM(  IF(buyinfo.insname = '车上人员责任险 - 乘客', buyinfo.SDEW, 0) ) AS  `车上人员责任险-乘客/不计免赔`,\n" +

                /*  -- 6.玻璃单独破碎险/保额  玻璃单独破碎险/保费 */
                "SUM(  IF(buyinfo.insname = '玻璃单独破碎险', buyinfo.parameter, 0) ) AS `玻璃单独破碎险/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '玻璃单独破碎险', buyinfo.discount_price, 0) ) AS `玻璃单独破碎险/保费`,\n" +

                /*  -- 7.车身划痕损失险/保额  车身划痕损失险/保费  车身划痕损失险/不计免赔*/
                "SUM(  IF(buyinfo.insname = '车身划痕损失险', buyinfo.parameter, 0) )  AS `车身划痕损失险/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '车身划痕损失险', buyinfo.discount_price, 0) )  AS `车身划痕损失险/保费`,\n" +
                "SUM(  IF(buyinfo.insname = '车身划痕损失险', buyinfo.SDEW, 0) ) AS  `车身划痕损失险/不计免赔`,\n" +

                /*  -- 8.自燃损失险/保费 */
                "SUM(  IF(buyinfo.insname = '自燃损失险', buyinfo.discount_price, 0) ) AS  `自燃损失险/保费` ,\n" +

                /*  -- 9.发动机涉水损失险/保费 */
                "SUM(  IF(buyinfo.insname = '发动机涉水损失险', buyinfo.discount_price, 0) ) AS `发动机涉水损失险/保费` ,\n" +

                /*  --  10. 倒车镜、车灯单独损失险/保费 */
                "SUM(  IF(buyinfo.insname = '倒车镜、车灯单独损失险', buyinfo.discount_price, 0) ) AS `倒车镜、车灯单独损失险/保费` ,\n" +

                /* --  11.新增设备损失险/保额  新增设备损失险/保费*/
                "SUM(  IF(buyinfo.insname = '新增设备损失险', buyinfo.parameter, 0) ) AS `新增设备损失险/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '新增设备损失险', buyinfo.discount_price, 0) ) AS `新增设备损失险/保费`,\n" +

                /*  --  12.指定修理厂险/保费 */
                "SUM(  IF(buyinfo.insname = '指定修理厂险', buyinfo.discount_price, 0) ) AS `指定修理厂险/保费`,\n" +

                /*  --  13.修理期间费用补偿险/保额     修理期间费用补偿险/保费*/
                "SUM(  IF(buyinfo.insname = '修理期间费用补偿险', buyinfo.parameter, 0) ) AS `修理期间费用补偿险/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '修理期间费用补偿险', buyinfo.discount_price, 0) ) AS `修理期间费用补偿险/保费` ,\n" +

                /*  --  14.车上货物责任险/保额  车上货物责任险/保费*/
                "SUM(  IF(buyinfo.insname = '车上货物责任险', buyinfo.parameter, 0) ) AS `车上货物责任险/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '车上货物责任险', buyinfo.discount_price, 0) )  AS `车上货物责任险/保费` ,\n" +

                /*  -- 15.精神损害抚慰金责任险/保额  精神损害抚慰金责任险/保费*/
                "SUM(  IF(buyinfo.insname = '精神损害抚慰金责任险', buyinfo.parameter, 0) ) AS `精神损害抚慰金责任险/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '精神损害抚慰金责任险', buyinfo.discount_price, 0) ) AS `精神损害抚慰金责任险/保费` ,\n" +

                /* -- 16.机动车损失保险无法找到第三方特约险/保费*/
                "SUM(  IF(buyinfo.insname = '机动车损失保险无法找到第三方特约险', buyinfo.discount_price, 0) ) AS `机动车损失保险无法找到第三方特约险/保费` ,\n" +

                /*  -- 17. 自定义险种一/保额  自定义险种一/保费\\t*/
                "SUM(  IF(buyinfo.insname = '自定义险种一', buyinfo.parameter, 0) ) AS `自定义险种一/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '自定义险种一', buyinfo.discount_price, 0) ) AS `自定义险种一/保费` ,\n" +

                /*  -- 18.自定义险种二/保额  自定义险种二/保费  附加险/不计免赔*/
                "SUM(  IF(buyinfo.insname = '自定义险种二', buyinfo.parameter, 0) ) AS  `自定义险种二/保额`,\n" +
                "SUM(  IF(buyinfo.insname = '自定义险种二', buyinfo.discount_price, 0) ) AS `自定义险种二/保费`,\n" +
                "SUM(  IF(buyinfo.insname = '附加险', buyinfo.SDEW, 0) ) AS `附加险/不计免赔`\n" +
                "FROM\n" +
                "t_insurance_car_info carinfo\n" +
                "left join t_ins_buy  insbuy ON carinfo.carid = insbuy.carid\n" +
                "left  JOIN t_endorsement_info eninfo on  insbuy.insID=eninfo.insID and eninfo.state='0'\n" +
                "left JOIN t_ins_buyinfo buyinfo on eninfo.pdID=buyinfo.pdID\n" +
                "left JOIN 用户 `用户` on 用户.用户名=carinfo.sales    \n" +
                "where  carinfo.permit=:permit \n";

        if (!Strings.isNullOrEmpty(icPolicyNo)) {//交强险保单号
            sql += "and  insbuy.forced_number like :icPolicyNo\n";
            params.put("icPolicyNo", "%" + icPolicyNo + "%");
        }
        if (!Strings.isNullOrEmpty(icInsuranceCpy)) {//交强险保险公司
            sql += "and insbuy.forced_ins_com = :icInsuranceCpy \n";
            params.put("icInsuranceCpy", icInsuranceCpy);
        }
        if (!Strings.isNullOrEmpty(bcPolicyNo)) {//商业险保单号
            sql += "and insbuy.bus_number like :bcPolicyNo\n";
            params.put("bcPolicyNo", "%" + bcPolicyNo + "%");
        }
        if (!Strings.isNullOrEmpty(bcInsuranceCpy)) {//商业险公司
            sql += "and insbuy.bus_ins_com =:bcInsuranceCpy\n";
            params.put("bcInsuranceCpy", bcInsuranceCpy);
        }
        if (!Strings.isNullOrEmpty(sale)) {//销售顾问名
            sql += "and 用户.`人员姓名` =:sale\n";
            params.put("sale", sale);
        }
        if (!Strings.isNullOrEmpty(cusName)) {
            sql += "and carinfo.insName like :cusName\n";
            params.put("cusName", "%" + cusName + "%");
        }
        if (!Strings.isNullOrEmpty(carBrand)) {
            sql += "and carinfo.brand like :carBrand \n";
            params.put("carBrand", "%" + carBrand + "%");
        }
        if (!Strings.isNullOrEmpty(carModel)) {
            sql += "and carinfo.model  like :carModel";
            params.put("carModel", "%" + carModel + "%");
        }
        sql += " GROUP BY  carinfo.carid";
        return this.getPage(sql, params, pageSize, pageIndex);

    }

    @Override
    public List getInsuranceOtherCarInfo(String vin) {
        Map params = Maps.newHashMap();
        params.put("vin", vin);
        String sql = "select \n" +
                "carinfo.carNumber,\n" +
                "carinfo.cartype,\n" +
                "carinfo.benz,\n" +
                "carinfo.brand,\n" +
                "carinfo.rank,\n" +
                "carinfo.model,\n" +
                "carinfo.modelshort,\n" +
                "carinfo.usetype,\n" +
                "carinfo.enginenumber,\n" +
                "carinfo.checkdate,\n" +
                "carinfo.peopleNo,\n" +
                "carinfo.weight,\n" +
                "carinfo.displacement,\n" +
                "carinfo.power, " +
                " carinfo.VIN , " +
                "  b.人员姓名 as userName\n" +
                " from t_insurance_car_info carinfo\n" +
                " LEFT JOIN `用户` b ON carinfo.sales =b.`用户名` " +
                "where carinfo.carid=:vin";
        return this.getList(sql, params);
    }

    @Override
    public List getInsuranceOtherICInfo(String vin) {
        Map params = Maps.newHashMap();
        params.put("vin", vin);
        String sql = " select \n" +
                " carinfo.insName,\n" +
                " carinfo.clientype,\n" +
                " carinfo.papersID,\n" +
                " carinfo.insTel,\n" +
                " carinfo.province,\n" +
                " carinfo.city,\n" +
                " carinfo.district,\n" +
                " carinfo.postcode,\n" +
                " carinfo.address,\n" +
                " insbuy.forced_op_time,\n" +
                " insbuy.forced_year,\n" +
                " insbuy.forced_ins_com,\n" +
                " insbuy.forced_cost ,\n" +
                " insbuy.forced_number,\n" +
                " insbuy.forced_insure_start,\n" +
                " insbuy.forced_insure_end,\n" +
                " eninfo.endorsementId,\n" +
                " eninfo.endorsement_time,\n" +
                " eninfo.remark,\n" +
                " eninfo.endorsement_type," +
                " eninfo.endorsementMoney," +
                " eninfo.pdID," +
                "  eninfo.year,\n" +
                " carinfo.sex,\n" +
                " insbuy.forced_end\n" +
                " FROM\n" +
                " t_insurance_car_info carinfo\n" +
                " left join t_ins_buy  insbuy ON carinfo.carid = insbuy.carid\n" +
                " left  JOIN t_endorsement_info eninfo on  insbuy.insID=eninfo.insID and eninfo.state='0' and eninfo.instype='0' \n" +
                " left JOIN 用户 `用户` on 用户.用户名=carinfo.sales\n" +
                " where   carinfo.carid=:vin";
        return this.getList(sql, params);
    }

    @Override
    public List getInsuranceBcInfo(String vin) {
        Map params = Maps.newHashMap();
        params.put("vin", vin);
        String sql =
                " select \n" +
                        " carinfo.insName,\n" +
                        " carinfo.clientype,\n" +
                        " carinfo.papersID,\n" +
                        " carinfo.insTel,\n" +
                        " carinfo.province,\n" +
                        " carinfo.city,\n" +
                        " carinfo.district,\n" +
                        " carinfo.postcode,\n" +
                        " carinfo.address,\n" +
                        " insbuy.bus_op_time,\n" +
                        " insbuy.bus_year,\n" +
                        " insbuy.bus_ins_com,\n" +
                        " SUM( if(buyinfo.insname<> '交强险' AND buyinfo.insname <> '车船税' AND  buyinfo.SDEW is not NULL,buyinfo.SDEW,0) ) +\n" +
                        "  SUM( if(buyinfo.insname<> '交强险' AND buyinfo.insname <> '车船税' AND  buyinfo.discount_price is not NULL,buyinfo.discount_price,0) )   bus_cost ,\n" +
                        " insbuy.bus_number,\n" +
                        " insbuy.bus_insure_start,\n" +
                        " insbuy.bus_insure_end,\n" +
                        " eninfo.endorsementId,\n" +
                        " eninfo.endorsement_time,\n" +
                        " eninfo.remark,\n" +
                        " eninfo.endorsement_type," +
                        " eninfo.endorsementMoney," +
                        " eninfo.year,\n" +
                        " eninfo.pdID,\n" +
                        " carinfo.sex,\n" +
                        " insbuy.bus_end\n" +
                        " FROM\n" +
                        " t_insurance_car_info carinfo\n" +
                        " left join t_ins_buy  insbuy ON carinfo.carid = insbuy.carid\n" +
                        " left  JOIN (select * from t_endorsement_info a where EXISTS (select * from (select max(pdID) pdID from t_endorsement_info group by insID,year,instype)b where a.pdID = b.pdID)) eninfo " +
                        "   on  insbuy.insID=eninfo.insID and eninfo.state='0' and eninfo.instype='1'    \n" +
                        "  left JOIN t_ins_buyinfo buyinfo on eninfo.pdID=buyinfo.pdID\n" +
                        " left JOIN 用户 `用户` on 用户.用户名=carinfo.sales\n" +
                        " where    carinfo.carid=:vin" +
                        "   GROUP BY   eninfo.year,eninfo.pdID ";
        return this.getList(sql, params);
    }

    @Override
    public List getInsuranceHistoryInfo(String carid) {
        Map params = Maps.newHashMap();
        params.put("carid", carid);
        String sql = " select \n" +
                " eninfo.instype,\n" +
                " eninfo.`year`,\n" +
                " eninfo.endorsementId,\n" +
                " eninfo.endorsement_time,\n" +
                " eninfo.ins_exit_time,\n" +
                " eninfo.endorsement_type,\n" +
                " eninfo.endorsementMoney" +
                " FROM\n" +
                " t_insurance_car_info carinfo\n" +
                " join t_ins_buy  insbuy ON carinfo.carid = insbuy.carid\n" +
                " left  JOIN t_endorsement_info eninfo on  insbuy.insID=eninfo.insID   \n" +
                " where carinfo.carid=:carid  " +
                " AND eninfo.endorsement_type <> '' and eninfo.endorsement_type is not null " +
                " ORDER BY eninfo.updatetime DESC\n";
        return this.getList(sql, params);
    }

    @Override
    public PageData getInsuranceAccountInfoOverWrite(String liveName, int pageSize, int pageIndex, String permit,
                                                     String tciNo, String vciNo, String tciCompany, String vciCompany,
                                                     String salesName, String status, String username, String userPhone,
                                                     String vin, String type, Date startEnterDate, Date endEnterDate,
                                                     Date startDueDate, Date endDueDate, Date startOrderDate,
                                                     Date endOrderDate, Date startExpectInvoiceDate,
                                                     Date endExpectInvoiceDate, String orderRelyType,
                                                     String forceInsuranceStartDate,String forceInsuranceEndDate,String commercialInsuranceStartDate,String commercialInsuranceEndDate) {
        String sql = " SELECT  bjbx.`第N年`,\n" +
                " a.保险编号,\n" +
                " a.投保类型,\n" +
                " b.state as 保险销售状态,\n" +
                " e.顾客姓名 as 客户姓名,\n" +
                " e.电话号码 as 客户电话,\n" +
                " f.人员姓名 as 销售顾问,\n" +
                " h.级别 as 意向车系,\n" +
                " h.车型 as 意向车型,\n" +
                " h.车型详细 as  意向车款,                 \n" +
                " a.交强险保险公司 as 交强险保险公司,                \n" +
                " a.商业险保险公司 as 商业险保险公司,\n" +
                " yy.商业险保单号 as 商业险保单号, \n" +  //改动
                " xx.交强险保单号 as 交强险保单号, \n" +//改动
                " IF(xx.交强险开始日期 IS NOT  NULL ,xx.交强险开始日期 , a.`交强险开始日期` ) 交强险开始日期,\n" +
                " IF(xx.交强险结束日期 IS NOT  NULL ,xx.交强险结束日期 , a.`交强险结束日期`   ) 交强险结束日期,\n" +
                " IF(yy.商业险开始日期 IS NOT  NULL ,yy.商业险开始日期 ,  a.`商业险开始日期`  ) 商业险开始日期,\n" +
                " IF(yy.商业险结束日期 IS NOT  NULL ,yy.商业险结束日期 , a.`商业险结束日期`   ) 商业险结束日期,\n" +
                " g.商业险出单日期,\n" +
                " g.交强险出单日期,\n" +
                " g.`登记日期`, " +
                "   ROUND((IFNULL((SELECT SUM(t.poundage) FROM lsh保险申请表附属_详细 t WHERE t.初始报价单号=g.`初始报价单号`),0)+a.`交强险手续费`),2) AS 交强险手续费 , \n" +
                "   ROUND((IFNULL((SELECT SUM(t.poundage) FROM lsh保险申请表附属_详细 t WHERE t.初始报价单号=g.`初始报价单号`),0)+ a.`商业险手续费`),2) AS 商业险手续费 ,\n " +


               /* "--   1.车辆损失险/保额    车辆损失险/保费    车辆损失险/不计免赔\n" +*/
                " SUM(  IF(bjbx.险种 = '车辆损失险', bjbx.参数, 0) ) AS `车辆损失险/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '车辆损失险', bjbx.折后价, 0) ) AS  `车辆损失险/保费`,\n" +
                " SUM(  IF(bjbx.险种 = '车辆损失险', bjbx.不计免赔, 0) ) AS  `车辆损失险/不计免赔`,\n" +

               /*  "--2. 商业三者责任险/保额  商业三者责任险/保费  商业三者责任险/不计免赔\n" +*/
                " SUM(  IF(bjbx.险种 = '商业三者责任险', bjbx.参数, 0) )  AS `商业三者责任险/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '商业三者责任险', bjbx.折后价, 0) )  AS `商业三者责任险/保费`,\n" +
                " SUM(  IF(bjbx.险种 = '商业三者责任险', bjbx.不计免赔, 0) ) AS `商业三者责任险/不计免赔`,\n" +

                /* "-- 3.全车盗抢险/保费  全车盗抢险/不计免赔 \n" +*/
                " SUM(  IF(bjbx.险种 = '全车盗抢险', bjbx.折后价, 0) )  AS `全车盗抢险/保费`,\n" +
                " SUM(  IF(bjbx.险种 = '全车盗抢险', bjbx.不计免赔, 0) )  AS `全车盗抢险/不计免赔`,\n" +

                /* "-- 4. 车上人员责任险-司机/保额  车上人员责任险-司机/保费  车上人员责任险-司机/不计免赔\n" +*/
                " SUM(  IF(bjbx.险种 = '车上人员责任险 - 司机', bjbx.参数, 0) )  AS  `车上人员责任险-司机/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '车上人员责任险 - 司机', bjbx.折后价, 0) ) AS  `车上人员责任险-司机/保费`,\n" +
                " SUM(  IF(bjbx.险种 = '车上人员责任险 - 司机', bjbx.不计免赔, 0) ) AS `车上人员责任险-司机/不计免赔`,\n" +

               /*  "-- 5. 车上人员责任险-乘客/保额  车上人员责任险-乘客/保费  车上人员责任险-乘客/不计免赔\n" +*/
                " SUM(  IF(bjbx.险种 = '车上人员责任险 - 乘客', bjbx.参数, 0) ) AS  `车上人员责任险-乘客/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '车上人员责任险 - 乘客', bjbx.折后价, 0) ) AS `车上人员责任险-乘客/保费`,\n" +
                " SUM(  IF(bjbx.险种 = '车上人员责任险 - 乘客', bjbx.不计免赔, 0) ) AS  `车上人员责任险-乘客/不计免赔`,\n" +

               /*  "-- 6.玻璃单独破碎险/保额  玻璃单独破碎险/保费 \n" +*/
                " SUM(  IF(bjbx.险种 = '玻璃单独破碎险', bjbx.参数, 0) ) AS `玻璃单独破碎险/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '玻璃单独破碎险', bjbx.折后价, 0) ) AS `玻璃单独破碎险/保费`,\n" +

               /*  "-- 7.车身划痕损失险/保额  车身划痕损失险/保费  车身划痕损失险/不计免赔\n" +*/
                " SUM(  IF(bjbx.险种 = '车身划痕损失险', bjbx.参数, 0) )  AS `车身划痕损失险/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '车身划痕损失险', bjbx.折后价, 0) )  AS `车身划痕损失险/保费`,\n" +
                " SUM(  IF(bjbx.险种 = '车身划痕损失险', bjbx.不计免赔, 0) ) AS  `车身划痕损失险/不计免赔`,\n" +

               /*  "-- 8.自燃损失险/保费 \n" +*/
                " SUM(  IF(bjbx.险种 = '自燃损失险', bjbx.折后价, 0) ) AS  `自燃损失险/保费` ,\n" +

               /*  "-- 9.发动机涉水损失险/保费 \n" +*/
                " SUM(  IF(bjbx.险种 = '发动机涉水损失险', bjbx.折后价, 0) ) AS `发动机涉水损失险/保费` ,\n" +

               /*  "--  10. 倒车镜、车灯单独损失险/保费 \n" +*/
                " SUM(  IF(bjbx.险种 = '倒车镜、车灯单独损失险', bjbx.折后价, 0) ) AS `倒车镜、车灯单独损失险/保费` ,\n" +

               /* "--  11.新增设备损失险/保额  新增设备损失险/保费\n" +*/
                " SUM(  IF(bjbx.险种 = '新增设备损失险', bjbx.参数, 0) ) AS `新增设备损失险/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '新增设备损失险', bjbx.折后价, 0) ) AS `新增设备损失险/保费`,\n" +

               /*  "--  12.指定修理厂险/保费 \n" +*/
                " SUM(  IF(bjbx.险种 = '指定修理厂险', bjbx.折后价, 0) ) AS `指定修理厂险/保费`,\n" +

               /*  "--  13.修理期间费用补偿险/保额     修理期间费用补偿险/保费\n" +*/
                " SUM(  IF(bjbx.险种 = '修理期间费用补偿险', bjbx.参数, 0) ) AS `修理期间费用补偿险/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '修理期间费用补偿险', bjbx.折后价, 0) ) AS `修理期间费用补偿险/保费` ,\n" +

               /*  "--  14.车上货物责任险/保额  车上货物责任险/保费\n" +*/
                " SUM(  IF(bjbx.险种 = '车上货物责任险', bjbx.参数, 0) ) AS `车上货物责任险/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '车上货物责任险', bjbx.折后价, 0) )  AS `车上货物责任险/保费` ,\n" +

               /*  "-- 15.精神损害抚慰金责任险/保额  精神损害抚慰金责任险/保费\n" +*/
                " SUM(  IF(bjbx.险种 = '精神损害抚慰金责任险', bjbx.参数, 0) ) AS `精神损害抚慰金责任险/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '精神损害抚慰金责任险', bjbx.折后价, 0) ) AS `精神损害抚慰金责任险/保费` ,\n" +

               /* "-- 16.机动车损失保险无法找到第三方特约险/保费\n" +*/
                " SUM(  IF(bjbx.险种 = '机动车损失保险无法找到第三方特约险', bjbx.折后价, 0) ) AS `机动车损失保险无法找到第三方特约险/保费` ,\n" +

               /*  "-- 17. 自定义险种一/保额  自定义险种一/保费\t\n" +*/
                " SUM(  IF(bjbx.险种 = '自定义险种一', bjbx.参数, 0) ) AS `自定义险种一/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '自定义险种一', bjbx.折后价, 0) ) AS `自定义险种一/保费` ,\n" +

               /*  "-- 18.自定义险种二/保额  自定义险种二/保费  附加险/不计免赔\n" +*/
                " SUM(  IF(bjbx.险种 = '自定义险种二', bjbx.参数, 0) ) AS  `自定义险种二/保额`,\n" +
                " SUM(  IF(bjbx.险种 = '自定义险种二', bjbx.折后价, 0) ) AS `自定义险种二/保费`,\n" +
                " SUM(  IF(bjbx.险种 = '附加险', bjbx.不计免赔, 0) ) AS `附加险/不计免赔`," +

                " b.logTime as 上次报价日期,\n" +
                " SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税' AND bjbx.第N年 = 1 and bjbx.不计免赔 is not NULL ,bjbx.不计免赔,0)) \n" +
                " +SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税' AND bjbx.第N年 = 1 and bjbx.折后价 is not NULL ,bjbx.折后价,0))  首年商业险,\n" +
                " SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税'   and bjbx.不计免赔 is not NULL ,bjbx.不计免赔,0)) \n" +
                " +SUM(IF( bjbx.险种 <> '交强险' AND bjbx.险种 <> '车船税'   and bjbx.折后价 is not NULL ,bjbx.折后价,0))  一保多年商业险保费,\n" +
                " SUM(if( bjbx.险种 = '交强险' AND bjbx.第N年 = 1,bjbx.折后价,0) ) 交强险,\n" +
                " SUM(if( bjbx.险种 = '车船税' AND bjbx.第N年 = 1,bjbx.折后价,0) ) 车船税,\n" +
                " m.报价次数 as 报价次数,\n" +
                " d.车架号 AS VIN码,\n" +
                " e.上次进店日期,\n" +
                " c.预计交车日期,   \n" +
                " c.`初始报价单号`, \n" +
                " g.操作人  ,  \n" +
                " case when xx.`批单类型` = '00' then '批增' " +
                "   when xx.`批单类型` = '01' then '批减' " +
                "   when xx.`批单类型` = '02' then '批退' " +
                " else '' end 批单类型,  \n" +
                " 用户.`人员姓名`  as  所属保险专员 \n" +
                " FROM  `报价单表` AS c\n" +
                " JOIN  vw_max_quotedcode_all AS x ON x.`报价编号`=c.`报价编号`\n" +
                " JOIN 报价保险表  AS a  ON  a.`保险编号`=c.`保险编号`\n" +
                " JOIN insurance_log AS b ON c.`初始报价单号`=b.orgQsn\n" +
                " JOIN  (SELECT *,MAX(ab.id) AS `保险单`  FROM  insurance_log AS ab  GROUP BY ab.orgQsn ) AS  y ON b.orgQsn=y.orgQsn \n" +
                " LEFT JOIN `lsh库存表`  AS d ON d.`库存编号`=c.`库存编号`\n" +
                " JOIN  `顾客记录汇总` AS e ON e.`顾客编号`=c.`顾客编号`\n" +
                " JOIN  `用户` AS f ON c.`销售顾问`=f.`用户名`\n" +
                " JOIN `lsh保险申请附属` AS g ON g.`初始报价单号`=c.`初始报价单号`\n" +
                " LEFT JOIN  `用户` AS 用户 ON g.`操作人`=用户.`用户名`" +
                " JOIN `lsh车型` AS  h ON c.`车型编号`=h.`车型编号`\n" +
                " LEFT JOIN 报价保险详细表 bjbx on  a.保险编号 = bjbx.保险编号\n" +
                " JOIN (SELECT sum(if(a.state=01 , 1, 0)) AS 报价次数 ,a.orgQsn FROM insurance_log AS a where a.state<>'00'  GROUP BY a.orgQsn) AS m ON m.orgQsn=b.orgQsn\n" +
                " LEFT JOIN 报价订单表 l ON l.订单编号 = c.订单编号\n" +
                " LEFT JOIN (select MAX(a.id) max_xx_id ,a.初始报价单号 from   lsh保险申请表附属_详细  as a   where a.`类型`=0  GROUP BY   初始报价单号   )  max_xx_id  ON max_xx_id.`初始报价单号`=c.`初始报价单号`\n" +
                " LEFT JOIN (select MAX(a.id) max_yy_id ,a.初始报价单号 from   lsh保险申请表附属_详细  as a   where a.`类型`=1  GROUP BY   初始报价单号   )  max_yy_id  ON max_yy_id.`初始报价单号`=c.`初始报价单号`\n" +
                " LEFT JOIN  lsh保险申请表附属_详细  xx ON xx.`id`= max_xx_id.max_xx_id" +
                " LEFT JOIN  lsh保险申请表附属_详细  yy ON yy.`id`= max_yy_id.max_yy_id" +
                " WHERE\n" +
                " b.id=y.`保险单`   AND b.state<>'00'  \n" +
                " AND  c.许可 =:permit  AND ( c.invalid_reason is null or c.invalid_reason <> '01'  )    ";
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        if (!Strings.isNullOrEmpty(tciNo)) {
            sql += " and xx.交强险保单号 like :tciNo \n";
            params.put("tciNo", "%" + tciNo + "%");
        }

        if (!Strings.isNullOrEmpty(vciNo)) {
            sql += " and xx.商业险保单号 like :vciNo \n";
            params.put("vciNo", "%" + vciNo + "%");
        }

        if (!Strings.isNullOrEmpty(tciCompany)) {
            sql += " and a.交强险保险公司 = :tciCompany \n";
            params.put("tciCompany", tciCompany);
        }

        if (!Strings.isNullOrEmpty(vciCompany)) {
            sql += " and a.商业险保险公司 = :vciCompany \n";
            params.put("vciCompany", vciCompany);
        }

        if (!Strings.isNullOrEmpty(salesName)) {
            sql += " and f.人员姓名 like :salesName \n";
            params.put("salesName", "%" + salesName + "%");
        }

        if (!Strings.isNullOrEmpty(status)) {
            sql += " and b.state = :status \n";
            params.put("status", status);
        }

        if (!Strings.isNullOrEmpty(username)) {
            sql += " and e.顾客姓名 like :username \n";
            params.put("username", "%" + username + "%");
        }

        if (!Strings.isNullOrEmpty(userPhone)) {
            sql += " and e.电话号码 like :userPhone \n";
            params.put("userPhone", "%" + userPhone + "%");
        }
        if (!Strings.isNullOrEmpty(type)) {
            sql += " and h.车型 like :type \n";
            params.put("type", "%" + type + "%");
        }
        if (startOrderDate != null){
            sql += " and (g.交强险出单日期 >= :startOrderDate or g.商业险出单日期 >= :startOrderDate)\n";
            params.put("startOrderDate", startOrderDate);
        }
        if (endOrderDate != null){
            sql += " and (g.交强险出单日期 <= :endOrderDate or g.商业险出单日期 <= :endOrderDate)\n";
            params.put("endOrderDate", endOrderDate);
        }
        if (startExpectInvoiceDate != null){
            sql += " and l.预计开票日期 >= :startExpectInvoiceDate\n";
            params.put("startExpectInvoiceDate", startExpectInvoiceDate);
        }
        if (endExpectInvoiceDate != null){
            sql += " and l.预计开票日期 <= :endExpectInvoiceDate\n";
            params.put("endExpectInvoiceDate", endExpectInvoiceDate);
        }

        if (!Strings.isNullOrEmpty(orderRelyType)) {
            sql += " and xx.`批单类型`= :orderRelyType\n";
            params.put("orderRelyType", orderRelyType);
        }
        if (!StringUtils.isNullOrBlank(forceInsuranceStartDate)) {
            sql += "and g.交强险出单日期 >= :forceInsuranceStartDate ";
            params.put("forceInsuranceStartDate",forceInsuranceStartDate);
        }
        if (!StringUtils.isNullOrBlank(forceInsuranceEndDate)) {
            sql += "and g.交强险出单日期 <= :forceInsuranceEndDate ";
            params.put("forceInsuranceEndDate",forceInsuranceEndDate);
        }

        if (!StringUtils.isNullOrBlank(commercialInsuranceStartDate)) {
            sql += "and g.商业险出单日期 >= :commercialInsuranceStartDate ";
            params.put("commercialInsuranceStartDate",commercialInsuranceStartDate);
        }
        if (!StringUtils.isNullOrBlank(commercialInsuranceEndDate)) {
            sql += "and g.商业险出单日期 <= :commercialInsuranceEndDate ";
            params.put("commercialInsuranceEndDate",commercialInsuranceEndDate);
        }
        sql += "group by c.`初始报价单号`  ";
        sql += "order by    g.操作人 ,  b.logTime desc  ";

        return this.getPage(sql, params, pageSize, pageIndex);
    }

    @Override
    public List getInsuranceFeeByLicense(String license) {
        String sql = "select " +
                "	sxf.*, " +
                "   sz.brand," +
                "    sz.保险公司, " +
                "    sz.状态, " +
                "    case sz.状态 " +
                "		when '必选' then sz.启用状态 " +
                "		when '可选' then sy.使用状态 " +
                "    end as 本店状态 " +
                "	, " +
                " sz.`启用状态` 集团状态," +
                " sz.创建时间, " +
//                "    sy.更新时间, " +
                "     sxf.更新时间 AS 使用更新时间, " +
                "    sy.更新账号 " +
                " from LSH保险公司设置 sz " +
                " join LSH保险手续费 sxf on sz.保险公司编号 = sxf.保险公司编号 " +
                " join LSH保险公司使用 sy on sz.保险公司编号 = sy.保险公司编号 and sxf.许可 = sy.许可 and sy.许可 = :license, " +
                " ( select parent_id, brand from group_area where permit = :license) group_area " +
                "   where ( sz.area_id = group_area.parent_id or sz.area_id is null )" +
                "   AND LOCATE(sz.brand, group_area.brand) > 0 ";
        Map params = Maps.newHashMap();
        params.put("license", license);
        List data = this.getList(sql, params);
        return data;
    }

    @Override
    public  List getInsDefeatedResonRpt(){
        String sql=" SELECT * FROM vw_group_report_ins_defeat_base ";
        return this.getList(sql);
    }

    @Override
    public List getInsuianceChange(String inscode) {
        String sql="SELECT * FROM `报价保险详细表` a  WHERE a.`保险编号`= :inscode ";
        Map per = Maps.newHashMap();
        per.put("inscode",inscode);

        return this.getList(sql,per);
    }

    @Override
    public  List getInsuranceCompany(){
        String sql="SELECT\n" +
                "\tb.`保险公司编号`,b.`保险公司`,a.`许可` "+
                "FROM\n" +
                "\tlsh保险公司使用 a ,\n" +
                "\tlsh保险公司设置 b ,\n" +
                "\tlsh经销商表 c\n" +
                "WHERE\n" +
                "\ta.`保险公司编号` = b.`保险公司编号`\n" +
                "AND a.`许可` = c.`许可_BMBS`\n" +
                "AND b.`启用状态`='启用'\n" +
                "AND a.`使用状态`='启用'\n" +
                "group by b.`保险公司编号`\n" +
                "ORDER BY a.`保险公司编号` ASC";
        return this.getList(sql);
    }
}
